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?
