Re: Partitioned/inherited tables with check constraints causing slower query plans

On 4 May 2012 17:39, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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