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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
