pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery with limit goes from few ms to hours
7+ messages / 5 participants
[nested] [flat]
* Query with limit goes from few ms to hours
@ 2012-10-14 06:55 henk de wit <henk53602@hotmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: henk de wit @ 2012-10-14 06:55 UTC (permalink / raw)
To: pgsql-performance
On PG 9.1 and 9.2 I'm running the following query:
SELECT *FROM stream_store JOIN ( SELECT UNNEST(stream_store_ids) AS id FROM stream_store_version_index WHERE stream_id = 607106 AND version = 11 ) AS records USING (id)ORDER BY id DESC
This takes several (10 to 20) milliseconds at most.
When I add a LIMIT 1 to the end of the query, the query time goes to several hours(!).
The full version String of PG 9.1 is "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit". The 9.1 machine is a socket 771 dual quad core at 3.16Ghz with 64GB memory and 10 Intel x25M SSDs in a RAID5 setup on 2 ARECA 1680 RAID controllers. The "stream_store" table has 122 million rows and is partitioned. The array that's being unnested for the join has 27 entries.
Any idea? =
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with limit goes from few ms to hours
@ 2012-10-14 07:04 henk de wit <henk53602@hotmail.com>
parent: henk de wit <henk53602@hotmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: henk de wit @ 2012-10-14 07:04 UTC (permalink / raw)
To: pgsql-performance
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
Kind regards =
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with limit goes from few ms to hours
@ 2012-10-14 16:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: henk de wit <henk53602@hotmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2012-10-14 16:15 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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 pasted those 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with limit goes from few ms to hours
@ 2012-10-15 12:31 Robert Klemme <shortcutter@googlemail.com>
parent: henk de wit <henk53602@hotmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Robert Klemme @ 2012-10-15 12:31 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with limit goes from few ms to hours
@ 2012-10-15 17:50 henk de wit <henk53602@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: henk de wit @ 2012-10-15 17:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Hi,
> 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 pasted those 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.
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.
Thanks!
Kind regards =
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with limit goes from few ms to hours
@ 2012-10-23 18:33 Kevin Grittner <kgrittn@mail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2012-10-23 18:33 UTC (permalink / raw)
To: henk de wit <henk53602@hotmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with limit goes from few ms to hours
@ 2012-10-23 20:08 Steve Crawford <scrawford@pinpointresearch.com>
parent: Kevin Grittner <kgrittn@mail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Steve Crawford @ 2012-10-23 20:08 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@mail.com>; +Cc: henk de wit <henk53602@hotmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-10-23 20:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-14 06:55 Query with limit goes from few ms to hours henk de wit <henk53602@hotmail.com>
2012-10-14 07:04 ` henk de wit <henk53602@hotmail.com>
2012-10-14 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-10-15 17:50 ` henk de wit <henk53602@hotmail.com>
2012-10-15 12:31 ` Robert Klemme <shortcutter@googlemail.com>
2012-10-23 18:33 Re: Query with limit goes from few ms to hours Kevin Grittner <kgrittn@mail.com>
2012-10-23 20:08 ` Steve Crawford <scrawford@pinpointresearch.com>
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