Re: path toward faster partition pruning

On Wed, Sep 6, 2017 at 4:08 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/09/04 10:10, Amit Langote wrote:
>> On 2017/09/02 2:52, Robert Haas wrote:

>
> [PATCH 2/5] WIP: planner-side changes for partition-pruning
>
> This patch adds a stub get_partitions_for_keys in partition.c with a
> suitable interface for the caller to pass bounding keys extracted from the
> query and other related information.
>
> Importantly, it implements the logic within the planner to match query's
> scan keys to a parent table's partition key and form the bounding keys
> that will be passed to partition.c to compute the list of partitions that
> satisfy those bounds.
>

+ Node *leftop = get_leftop(clause);
+
+ if (IsA(leftop, RelabelType))
+ leftop = (Node *) ((RelabelType *) leftop)->arg;
+
+ if (equal(leftop, partkey))

It appears that this patch always assume that clause will be of form
"var op const", but it can also be "const op var"

That's the reason in below example where in both the queries condition
is same it can only prune in the first case but not in the second.

postgres=# explain select * from t where t.a < 2;
QUERY PLAN
--------------------------------------------------------
Append (cost=0.00..2.24 rows=1 width=8)
-> Seq Scan on t1 (cost=0.00..2.24 rows=1 width=8)
Filter: (a < 2)
(3 rows)

postgres=# explain select * from t where 2>t.a;
QUERY PLAN
--------------------------------------------------------
Append (cost=0.00..4.49 rows=2 width=8)
-> Seq Scan on t1 (cost=0.00..2.24 rows=1 width=8)
Filter: (2 > a)
-> Seq Scan on t2 (cost=0.00..2.25 rows=1 width=8)
Filter: (2 > a)
(5 rows)

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com