Re: Index ot being used - Mailing list pgsql-performance

Tom Lane wrote:
> Madison Kelly <linux@alteeve.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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-