Re: performance tuning - Mailing list pgsql-general

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

Hope this helps,

> 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>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.