Re: performance tuning

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?

NOTICE: QUERY PLAN:

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

-------------------

NOTICE: QUERY PLAN:

Aggregate (cost=51432.61..51432.61 rows=1 width=12) (actual time=22158.72..22158.72
rows=1 loops=1)
-> Merge Join (cost=50838.66..51431.69 rows=367 width=12) (actual
time=21266.19..22156.59 rows=254 loops=1)
-> Sort (cost=12208.53..12208.53 rows=15223 width=6) (actual
time=3297.82..3395.68 rows=17912 loops=1)
-> Seq Scan on u (cost=0.00..11151.01 rows=15223 width=6) (actual
time=0.08..3060.66 rows=17912 loops=1)
-> Sort (cost=38630.13..38630.13 rows=136667 width=6) (actual
time=17967.08..18383.46 rows=140492 loops=1)
-> Seq Scan on d (cost=0.00..25751.95 rows=136667 width=6) (actual
time=0.06..14766.69 rows=140492 loops=1)
Total runtime: 22285.74 msec

These are simplified versions of my query designed to highlight that particular join. In
my real query the results are even more out of balance:

Sort (cost=95409.39..95409.39 rows=3 width=641) (actual time=47092.77..47092.78 rows=26
loops=1)
<snip>
vs.
Sort (cost=205121.27..205121.27 rows=3 width=641) (actual time=6461.65..6461.66 rows=26
loops=1)
<snip>