On 4 May 2012 17:39, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > I get a reasonable-looking plan when I try to duplicate this issue in > 9.1 branch tip. I think the reason you're not getting the right > behavior is that you are missing this as-yet-unreleased patch: > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=ef03b34550e3577c4be3baa25b70787f5646c57b > which means it can't figure out that the available index on the child > table produces the desired sort order. If you're in a position to > compile from source, a current nightly snapshot of the 9.1 branch > ought to work for you; otherwise, wait for 9.1.4. Thanks, this did the trick - here's the output when I switched to 9.1 snapshot: ircevents=# select version(); version ---------------------------------------------------------------------------------------------- PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.4.5-8) 4.4.5, 64-bit (1 row) ircevents=# explain analyze SELECT id, type, json FROM ircevents WHERE buffer = 116780 AND id BETWEEN 1325458800000000 AND 1330642800000000 ORDER BY id DESC LIMIT 100; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- Limit (cost=0.05..202.45 rows=100 width=135) (actual time=176.429..237.766 rows=100 loops=1) -> Result (cost=0.05..68161.99 rows=33677 width=135) (actual time=176.426..237.735 rows=100 loops=1) -> Merge Append (cost=0.05..68161.99 rows=33677 width=135) (actual time=176.426..237.708 rows=100 loops=1) Sort Key: public.ircevents.id -> Sort (cost=0.01..0.02 rows=1 width=72) (actual time=0.009..0.009 rows=0 loops=1) Sort Key: public.ircevents.id Sort Method: quicksort Memory: 25kB -> Seq Scan on ircevents (cost=0.00..0.00 rows=1 width=72) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((id >= 1325458800000000::bigint) AND (id <= 1330642800000000::bigint) AND (buffer = 116780)) -> Index Scan Backward using ircevents_201201_idx on ircevents_201201 ircevents (cost=0.00..8811.15 rows=2181 width=133) (actual time=76.356..136.91 7 rows=12 loops=1) Index Cond: ((buffer = 116780) AND (id >= 1325458800000000::bigint) AND (id <= 1330642800000000::bigint)) -> Index Scan Backward using ircevents_201202_idx on ircevents_201202 ircevents (cost=0.00..54963.83 rows=30613 width=135) (actual time=47.333..48.0 25 rows=88 loops=1) Index Cond: ((buffer = 116780) AND (id >= 1325458800000000::bigint) AND (id <= 1330642800000000::bigint)) -> Index Scan Backward using ircevents_201203_idx on ircevents_201203 ircevents (cost=0.00..3629.22 rows=882 width=134) (actual time=52.724..52.724 rows=0 loops=1) Index Cond: ((buffer = 116780) AND (id >= 1325458800000000::bigint) AND (id <= 1330642800000000::bigint)) Total runtime: 237.889 ms (16 rows) So yes, it's using "index scan backwards" - and fixes my problem, thanks! Bit reluctant to put the machine into production with a non-release version of postgres, I'll wait for 9.1.4 to make an official appearance. Regards, RJ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance