Query with limit goes from few ms to hours - Mailing list pgsql-performance

From henk de wit
Subject Query with limit goes from few ms to hours
Date
Msg-id COL104-W53D001BC091CFA02343989F5720@phx.gbl
Whole thread Raw
Responses Re: Query with limit goes from few ms to hours  (henk de wit <henk53602@hotmail.com>)
List pgsql-performance
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?

pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Index over all partitions (aka global index)?
Next
From: henk de wit
Date:
Subject: Re: Query with limit goes from few ms to hours