Re: Query with limit takes hours

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...