Re: [HACKERS] Runtime Partition Pruning

On 21 December 2017 at 22:01, David Rowley <david.rowley@2ndquadrant.com> wrote:
> I've attached the latest version of the patch. This is based
> on Amit's v15 of faster-partition-pruning [1] which I found to cleanly
> apply to f94eec490

Well, that went out of date pretty quickly. Amit has now posted v16 of
the faster partition pruning patch [1] which conflicts with my changes
in my v2 patch.

I've attached a new version of the patch to resolve these conflicts.

In v16 Amit added some code to eliminate LIST partitions when there
are not equals expressions which cause all the items in the IN list to
be unmatchable.

This appears to work fine with the run-time pruning in the attached, per:

create table ta (a int not null) partition by list (a);
create table ta1 partition of ta for values in(1,2);
create table ta2 partition of ta for values in(3,4);
explain select * from ta where a <> 1 and a <> 2;
create table ta_null partition of ta for values in(null);

prepare q1 (int) as select * from ta where a <> 1 and a <> $1;

explain (costs off, analyze) execute q1(2);
                           QUERY PLAN
-----------------------------------------------------------------
 Append (actual time=0.012..0.012 rows=0 loops=1)
   ->  Seq Scan on ta1 (never executed)
         Filter: ((a <> 1) AND (a <> $1))
   ->  Seq Scan on ta2 (actual time=0.012..0.012 rows=0 loops=1)
         Filter: ((a <> 1) AND (a <> $1))
 Planning time: 0.019 ms
 Execution time: 0.057 ms
(7 rows)


explain (costs off, analyze) execute q1(1);
                           QUERY PLAN
-----------------------------------------------------------------
 Append (actual time=0.017..0.017 rows=0 loops=1)
   ->  Seq Scan on ta1 (actual time=0.013..0.013 rows=0 loops=1)
         Filter: ((a <> 1) AND (a <> $1))
   ->  Seq Scan on ta2 (actual time=0.003..0.003 rows=0 loops=1)
         Filter: ((a <> 1) AND (a <> $1))
 Planning time: 0.021 ms
 Execution time: 0.068 ms
(7 rows)

[1] https://www.postgresql.org/message-id/5ebae4cf-8145-975c-ad75-16eb7f756f32%40lab.ntt.co.jp

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services