From: Jeremy Harris <jgh@wizmail.org>
To: pgsql-performance@postgresql.org
Subject: Re: issue with query optimizer when joining two partitioned tables
Date: Sun, 10 Jul 2011 16:46:25 +0100
Message-ID: <4E19C951.1050109@wizmail.org> (raw)
In-Reply-To: <7806.1310233430@sss.pgh.pa.us>
References: <CAOpcnr_nx2OAoyjJknUdeJsvFame=_K0bfKgzTjVq4G+=7yxZA@mail.gmail.com>
<4E180920.8040806@enterprisedb.com>
<7806.1310233430@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jgh@wizmail.org
Subject: Re: issue with query optimizer when joining two partitioned tables
In-Reply-To: <4E19C951.1050109@wizmail.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox