Re: Query with limit takes hours

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?