Tom Lane wrote:
Madison Kelly <[EMAIL PROTECTED]> writes:
Bruno Wolff III wrote:
Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan.
Given the fairly large number of rows being selected, it seems likely
that the planner thinks this is faster than an indexscan. It could
be right, too. Have you tried "set enable_seqscan = off" to see if
the index is used then? If so, is it faster or slower? Comparing
EXPLAIN ANALYZE results with enable_seqscan on and off would be useful.
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu=> set enable_seqscan = off; SET
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
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using file_info_7_display_idx on file_info_7
(cost=0.00..83171.78 rows=25490 width=119) (actual
time=141.405..1700.459 rows=25795 loops=1)
Index Cond: ((file_type)::text = 'd'::text)
Total runtime: 1851.366 ms
(3 rows)
tle-bu=> set enable_seqscan = on; SET
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=13605.185..13728.436 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.048..2018.996 rows=25795 loops=1)
Filter: ((file_type)::text = 'd'::text)
Total runtime: 13865.830 ms
(5 rows)
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
