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 Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to