Message-ID: <4E19C951.1050109@wizmail.org>
Date: Sun, 10 Jul 2011 16:46:25 +0100
From: Jeremy Harris <jgh@wizmail.org>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.21) Gecko/20090320 Fedora/2.0.0.21-1.fc9
	Thunderbird/2.0.0.21 Mnenhy/0.7.5.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: issue with query optimizer when joining two partitioned
 tables
References: 
 <CAOpcnr_nx2OAoyjJknUdeJsvFame=_K0bfKgzTjVq4G+=7yxZA@mail.gmail.com>
	<4E180920.8040806@enterprisedb.com> <7806.1310233430@sss.pgh.pa.us>
In-Reply-To: <7806.1310233430@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2011-07-09 18:43, Tom Lane wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
>> On 09.07.2011 00:36, Anish Kejariwal wrote:
>>> My guess as to what happened:
>>> -because the icecream parent table has zero records, the query optimizer
>>> chooses the incorrect execution plan
>>> -when I do select * from icecream, the optimizer now knows how many records
>>> are really in the icecream table, by knowing that the icecream table has
>>> partitions.
>
>> "select * from icecream" won't have any direct effect on the
>> optimization of subsequent queries. What probably happened is that
>> autoanalyze ran in the background while you ran that select, and
>> analyzed some of the partitions. Simply waiting a while would've had the
>> same effect.
>
> Yeah.  Also, the reason that a manual vacuum on icecream changes things
> yet again is that in 9.0 and up, we have a notion of summary stats
> across the whole inheritance tree, but autoanalyze hasn't been taught to
> gather those.  The manual command on the parent table does gather them,
> though.

Is stats-gathering significantly more expensive than an FTS?   Could an FTS
update stats as a matter of course (or perhaps only if enough changes in table)?
-- 
Jeremy