Re: In progress INSERT wrecks plans on table - Mailing list pgsql-performance

From Mark Kirkwood
Subject Re: In progress INSERT wrecks plans on table
Date
Msg-id 518DD007.4000006@catalyst.net.nz
Whole thread Raw
In response to Re: In progress INSERT wrecks plans on table  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
On 11/05/13 01:30, Tom Lane wrote:
> Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
>> Unfortunately a trigger will not really do the job - analyze ignores in
>> progress rows (unless they were added by the current transaction), and
>> then the changes made by analyze are not seen by any other sessions. So
>> no changes to plans until the entire INSERT is complete and COMMIT
>> happens (which could be a while - too long in our case).
>
> I'm not sure I believe the thesis that plans won't change at all.
> The planner will notice that the physical size of the table is growing.
> That may not be enough, if the table-contents statistics are missing
> or completely unreflective of reality, but it's something.
>
> It is true that *already cached* plans won't change until after an
> ANALYZE is done (the key point there being that ANALYZE sends out a
> shared-inval message to force replanning of plans for the table).
> Conceivably you could issue concurrent ANALYZEs occasionally while
> the INSERT is running, not so much to update the stats --- because
> they wouldn't --- as to force cached-plan invalidation.

Yeah - true, I was focusing on the particular type of query illustrated
in the test case - pretty much entirely needing updated selectivity
stats for a column, which wouldn't change unfortunately.

Cheers

Mark



pgsql-performance by date:

Previous
From: Misa Simic
Date:
Subject: Re: PostgreSQL planner
Next
From: Josh Berkus
Date:
Subject: Re: Setting vacuum_freeze_min_age really low