Re: Query with limit takes hours

[Vincent <vinny@xxxxxxxxx>]

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