Re: Index ot being used

Bruno Wolff III wrote:
> I am guessing you mean 'file_type' instead of 'file_info'.
>
> To do this efficiently you want an index on (file_type, file_parent_dir,
> file_name). Currently you only have an index on (file_parent_dir, file_name)
> which won't help for this query. You also need to order by file_type
> even though it will be constant for all of the returned rows in order
> to help out the planner. This will allow an index scan over the desired
> rows that returns them in the desired order.
>
> Please actually try this before changing anything else.

   If I follow then I tried it but still got the sequential scan. Here's
the index and query (copied from the 'psql' shell):


tle-bu=> \d file_info_7_display_idx  Index "public.file_info_7_display_idx"
      Column      |         Type
-----------------+----------------------
  file_type       | character varying(2)
  file_parent_dir | text
  file_name       | text
btree, for table "public.file_info_7"

tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC,
file_parent_dir ASC, file_name ASC;
                                                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=14810.92..14874.65 rows=25490 width=119) (actual
time=15523.767..15731.136 rows=25795 loops=1)
    Sort Key: file_type, file_parent_dir, file_name
    ->  Seq Scan on file_info_7  (cost=0.00..11956.84 rows=25490
width=119) (actual time=0.132..2164.757 rows=25795 loops=1)
          Filter: ((file_type)::text = 'd'::text)
  Total runtime: 15884.188 ms
(5 rows)


   If I follow all three 'ORDER BY...' items match the three columns in
the index.

   Again, thanks!

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-