Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query with limit takes hours

Query with limit takes hours

From
henk de wit
Date:
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?

Re: Query with limit takes hours

From
Vincent
Date:
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...