Pseudo-Solved was: (Re: Index ot being used) - Mailing list pgsql-performance

Bruno Wolff III wrote:
> 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.

Hmm,

   In this case I am trying to avoid modifying 'postgres.conf' and am
trying to handle any performance tweaks within my program through SQL
calls. This is because (I hope) my program will be installed by many
users and I don't want to expect them to be able/comfortable playing
with 'postgres.conf'. I do plan later though to create a section in the
docs with extra tweaks for more advanced users and in that case I will
come back to this and try/record just that.

   In the mean time Tom's recommendation works from perl by calling:

$DB->do("SET ENABLE_SEQSCAN TO OFF") || die...
<query...>
$DB->do("SET ENABLE_SEQSCAN TO ON") || die...

   Forces the index to be used. It isn't clean but it works for now and
I don't need to do anything outside my program.

   Lacking any other ideas, thank you very, very much for sticking with
this and helping me out!

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-