Обсуждение: Query with limit goes from few ms to hours

henk de wit <henk53602@hotmail.com> writes:
> For some reason the mailinglist software seems to block the email as soon as the planner details are in it, so I
pastedthose on pastebin.com: http://pastebin.com/T5JTwh5T 

You need a less horrid estimate for the join size.  Possibly an ANALYZE
on the parent table (stream_store) would help.

            regards, tom lane

Hi Henk,

On Sun, Oct 14, 2012 at 9:04 AM, henk de wit <henk53602@hotmail.com> wrote:
> Hi,
>
> For some reason the mailinglist software seems to block the email as soon as
> the planner details are in it, so I pasted those on pastebin.com:
> http://pastebin.com/T5JTwh5T

Just an additional data point: for whatever reason your email was
placed in my GMail spam folder.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

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.

-Kevin

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