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?