From: Steve Crawford <scrawford@pinpointresearch.com>
To: Kevin Grittner <kgrittn@mail.com>
Cc: henk de wit <henk53602@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query with limit goes from few ms to hours
Date: Tue, 23 Oct 2012 13:08:15 -0700
Message-ID: <5086F92F.50406@pinpointresearch.com> (raw)
In-Reply-To: <20121023183316.179560@gmx.com>
References: <20121023183316.179560@gmx.com>
On 10/23/2012 11:33 AM, Kevin Grittner wrote:
> henk de wit wrote:
>
>> Well, what do you know! That did work indeed. Immediately after the
>> ANALYZE on that parent table (taking only a few seconds) a fast
>> plan was created and the query executed in ms again. Silly me, I
>> should have tried that earlier.
> Of course, if your autovacuum settings are aggressive enough, you
> should gernerally not need to run ANALYZE explicitly. You should
> double-check that autovacuum is turned on and configured at least as
> aggressively as the default settings, or you will probably get little
> surprises like this when you least expect them.
>
>
The exception I'd make to Kevin's good advice is for cases when a
process makes substantial statistics-altering changes to your data (bulk
insert/delete/update) immediately followed by a query against the
updated table(s). In those cases there is a good possibility that the
statistics will not have been automatically updated before the
subsequent query is planned so an explicit ANALYZE between the update
and the query can be of value.
Cheers,
Steve
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: scrawford@pinpointresearch.com, kgrittn@mail.com, henk53602@hotmail.com
Subject: Re: Query with limit goes from few ms to hours
In-Reply-To: <5086F92F.50406@pinpointresearch.com>
* 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