Re: Index ot being used

On Mon, Jun 13, 2005 at 15:05:00 -0400,
  Madison Kelly <linux@alteeve.com> wrote:
> 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...

The two things you probably want to look at are (in postgresql.conf):
effective_cache_size = 10000    # typically 8KB each
random_page_cost = 2            # units are one sequential page fetch cost

Increasing effective cache size and decreasing the penalty for random
disk fetches will favor using index scans. People have reported that
dropping random_page_cost from the default of 4 to 2 works well.
Effective cache size should be set to some reasonable estimate of
the memory available on your system to postgres, not counting that
set aside for shared buffers.

However, since the planner thought the index scan plan was going to be 6 times
slower than the sequential scan plan, I don't know if tweaking these values
enough to switch the plan choice won't cause problems for other queries.