Re: performance tuning

On Wed, Dec 04, 2002 at 05:00:37PM -0500, Joseph Shraibman wrote:
> Martijn van Oosterhout wrote:
> >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.

Odd how it is doing a sort after the index scan. Perhaps you need to look at
your query and see if you can encourage the right join order.

> 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

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