Re: issue with query optimizer when joining two partitioned tables

From: Jeremy Harris
Subject: Re: issue with query optimizer when joining two partitioned tables
Date: ,
Msg-id: 4E19C951.1050109@wizmail.org
(view: Whole thread, Raw)
In response to: Re: issue with query optimizer when joining two partitioned tables  (Tom Lane)
List: pgsql-performance


On 2011-07-09 18:43, Tom Lane wrote:
> Heikki Linnakangas<>  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


pgsql-performance by date:

From: Samuel Gendler
Date:
Subject: Re: Statistics and Multi-Column indexes
From: Craig Ringer
Date:
Subject: Re: query total time im milliseconds