Re: Partitioned/inherited tables with check constraints causing slower query plans - Mailing list pgsql-performance

On 4 May 2012 17:39, Tom Lane <tgl@sss.pgh.pa.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