Message-ID: <20070109141516.59227.qmail@web26307.mail.ukl.yahoo.com>
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.no;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=R9keO1RyaWSdon6Cwgc1uQ/IQds6iIlTbRYXggxPdiLvGFUs/wL7LXo9ZOVU/wHpgjLezn+MbD4HgOiIAM5+ig61phod2t4QiUk6sAf66ziq1EHG2hGKrkH6aT9j5NjyB3IkPpPADRpSog3k3YWapDrQjqlxtR2Rab3+7SnoKjo=;
Date: Tue, 9 Jan 2007 15:15:15 +0100 (CET)
From: =?iso-8859-1?q?Rolf=20=D8stvik?= <rolfostvik@yahoo.no>
Subject: Re: Worse perfomance on 8.2.0 than on 7.4.14
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1168078625.3655.97.camel@silverbirch.site>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


--- Simon Riggs <simon@2ndquadrant.com> skrev:

> 
> The distribution of rows with those values also makes a difference to
> the results. ANALYZE assumes that all values are randomly distributed
> within the table, so if the values are clumped together for whatever
> reason the ndistinct calc is less likely to take that into account.

This is an important factor.

As a summary, one table is defined like this:

Table "public.step_result_subset"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | integer | not null
 uut_result  | integer | 
 step_parent | integer | 
Indexes:
    "step_result_subset_pkey" PRIMARY KEY, btree (id)
    "step_result_subset_parent_key" btree (step_parent)
    "step_result_uut_result_idx" btree (uut_result)

The values in step_result_subset.uut_result is clumped together (between 10 and 1000 of same
value, and also increasing through the table).
The rows where step_result_subset.step_parent is 0 (a special case) is distributed within the
table. 

Even when i set statistics on test_result_subset.uut_result to 1000 7.4.14 picks a better plan
than 8.2.0 for some returned datasets. The best results for both 7.4.14 and 8.2.0 is if i remove
the index step_result_subset_parent_key.
I will have to check if other queries which uses step_result_subset.step_parent will be "broken"
by removing the index but i think it should be ok.


I have gotten some ideas from this thread , read some more documentation, read the archives, and
tested other queries and will try to speed up some more advance queries.

Thanks everyone.

best regards
Rolf Østvik


__________________________________________________
Bruker du Yahoo!?
Lei av spam?  Yahoo! Mail har den beste spambeskyttelsen 
http://no.mail.yahoo.com