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?