Re: Index ot being used

Tom Lane wrote:
> Madison Kelly <linux(at)alteeve(dot)com> 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-