From: Vincent <vinny@xs4all.nl>
To: pgsql-admin@postgresql.org
Subject: Re: Query with limit takes hours
Date: Sun, 14 Oct 2012 10:10:29 +0200
Message-ID: <507A7375.7000903@xs4all.nl> (raw)
In-Reply-To: <COL104-W2612EF566F12BD42D730FFF5720@phx.gbl>
References: <COL104-W2612EF566F12BD42D730FFF5720@phx.gbl>
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...
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-admin@postgresql.org
Cc: vinny@xs4all.nl
Subject: Re: Query with limit takes hours
In-Reply-To: <507A7375.7000903@xs4all.nl>
* 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