pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery with limit takes hours
2+ messages / 2 participants
[nested] [flat]
* Query with limit takes hours
@ 2012-10-14 06:56 henk de wit <henk53602@hotmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: henk de wit @ 2012-10-14 06:56 UTC (permalink / raw)
To: pgsql-admin
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] 2+ messages in thread
* Re: Query with limit takes hours
@ 2012-10-14 08:10 Vincent <vinny@xs4all.nl>
parent: henk de wit <henk53602@hotmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Vincent @ 2012-10-14 08:10 UTC (permalink / raw)
To: pgsql-admin
On 10/14/2012 08:56 AM, henk de wit wrote:
> 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?
I'd look at the difference in the output of EXPLAIN for both queries,
that's bound to tell you something...
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-10-14 08:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-14 06:56 Query with limit takes hours henk de wit <henk53602@hotmail.com>
2012-10-14 08:10 ` Vincent <vinny@xs4all.nl>
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