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