Re: performance tuning

What do you mean join order? There are only two tables in this simplified query.
It looks like this:
SELECT COUNT(distinct d.ukey) FROM u, d WHERE d.ukey = u.ukey AND <other conditions on
d and u>

Martijn van Oosterhout wrote:
> 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
>
>

--
Joseph Shraibman
joseph(at)xtenit(dot)com
Increase signal to noise ratio. http://xis.xtenit.com