Re: performance tuning

Martijn van Oosterhout wrote:
> On Wed, Dec 04, 2002 at 02:15:35PM -0500, Joseph Shraibman wrote:
>
>>Martijn van Oosterhout wrote:
>>
>>>On Tue, Dec 03, 2002 at 09:48:46PM -0500, Joseph Shraibman wrote:
>>>
>>>
>>>>Joseph Shraibman wrote:
>>>>
>>>>
>>>>>Since postgres
>>>>>seems to think that the nested loop takes so long do I have to lower
>>>>>cpu_operator_cost to get postgres to use the nested loop?
>>>>
>>>>To answer my own question that doesn't work. I've kept playing around
>>>>with different paramaters with different variables but I can't find
>>>>anything except disabling seqscans.
>>>>
>>>>This is really annoying, because *all* of my queries suddenly slowed down
>>>>at the same time. What can I do?  Is there something I can change in the
>>>>source to have nested loops seem cheaper? I haven't found anything.
>>>
>>>
>>>What does explain analyze tell you?
>
>
> Hmm, the row counts don't seem to be too far off but it's overestimating the
> cost of your index scans. As the other poster mentioned try:
>
> set seq_scan=[on|off]
> set random_page_cost = 0.5..2.0
>
Setting it to .2 got it to use the index on the d table, setting it to .02 got it to use
an index on both. But now it is using a merge join instead of a nested loop.

Aggregate  (cost=23244.99..23244.99 rows=1 width=12)
   ->  Merge Join  (cost=1645.39..23244.08 rows=367 width=12)
         ->  Sort  (cost=1645.39..1645.39 rows=15223 width=6)
               ->  Index Scan using u_p_key on u  (cost=0.00..587.86 rows=15223 width=6)
         ->  Index Scan using d_pkey on d  (cost=0.00..21005.66 rows=136667 width=6)

where before just setting enable_seqscan = false  gave a nested loop:

Aggregate  (cost=102546.41..102546.41 rows=1 width=12) (actual time=16863.09..16863.09
rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..102545.49 rows=367 width=12) (actual
time=1034.46..16861.51 rows=254 loops=1)
         ->  Index Scan using u_p_key on u  (cost=0.00..43483.93 rows=15223 width=6)
(actual time=0.29..495.12 rows=17912 loops=1)
         ->  Index Scan using d_pkey on directory d  (cost=0.00..3.86 rows=1 width=6)
(actual time=0.90..0.91 rows=1 loops=17912)
Total runtime: 16863.26 msec

What makes postgres choose one or the other, and is it likely to hurt performance? I can't
to an explain analyse right now because the database is being used heavliy right now. I'll
do one later.