Re: [HACKERS] path toward faster partition pruning

On 4 April 2018 at 13:13, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> There might be another issue with the patch too, but I'll send a
> separate email about that.

In the current version of the patch the following comment exists:

/*
* Fall-through for a NOT clause, which is handled in
* match_clause_to_partition_key().
*/

The only real handling of NOT clauses is in
match_boolean_partition_clause() which just handles NOT(true) or
NOT(false).

It's true that the const simplification code will generally rewrite
most NOT(clause) to use the negator operator, but if the operator does
not have a negator it can't do this.

We probably don't have any built-in operators which are members of a
btree opclass which have no negator, but it's simple enough to modify
the citext extension by commenting out the NEGATOR lines in
citext--1.4--1.5.sql.

create extension citext;
create table listp(a citext) partition by list(a citext_pattern_ops);
create table listp_1 partition of listp for values in('1');
explain select * from listp where not (a ~>~ '0' and a ~<~ '2');
QUERY PLAN
--------------------------------------------------------------------------
Append (cost=0.00..36.45 rows=1209 width=32)
-> Seq Scan on listp_1 (cost=0.00..30.40 rows=1209 width=32)
Filter: ((NOT (a ~>~ '0'::citext)) OR (NOT (a ~<~ '2'::citext)))
(3 rows)

At the moment pruning does not work for this case at all. Perhaps it should?

I imagine it might be possible to re-work the COMBINE_INVERT code so
that it becomes a flag of the combine step rather than a step operator
type. It should then be possible to invert COMBINE_UNION for
NOT(clause1 OR clause2) and COMBINE_INTERSECT on NOT(clause1 AND
clause2).

IOW, it might not take too many lines of code to put this right.
Probably the bulk of the work would be writing a test with a btree
opclass that will allow us to have the planner not invert the clause
during const folding.

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