Runtime Partition Pruning

I have been working on implementing the runtime partition pruning
which would increase the performance of queries involving partitioned
table to a great extent.

PFA the POC which can be applied over Amit's patch for faster
partition pruning [1] and Dilip's refactor patch [2] on commit
2c74e6c1dcc5002fa8b822e5757f6c95d899fb7a.

[1] https://www.postgresql.org/message-id/e02923ea-a117-a6ad-6a3e-ea5e1ba41ece%40lab.ntt.co.jp

[2] https://www.postgresql.org/message-id/CAFiTN-tGnQzF_4QtbOHT-3hE%3DOvNaMfbbeRxa4UY0CQyF0G8gQ%40mail.gmail.com

There were a couple of things that need improvement/opinion:
In get_rel_partition_info, we store minop and maxop for each partition
key. For the equality case, which is most common, both would store the
same value. We could make it better by storing equal (bound, bound,
....) instead repeating the same values.

get_partitions_for_keys currently returns the list of partitions valid
for the given keys but for a table with many partitions this list
would be very long so maybe for range qual ( key > a & key < b ) we
could only store the min and max partition number and increment
as_whichplan by 1 till we reach max partition number. For
non-continuous partitions, we would still need the list.

Currently, the partitions numbers are recalculated whenever the
ChgParam is set, This can be optimised by skipping this step when only
a non-partition key column has changed; reusing the existing
partitions selected.

Others:
- better handling of multiple key
- allow use of expression in the quals.
- To use min_incl, max_incl properly in get_partitions_for_keys.
- pruning during function calls.

Currently with patch, during NestLoop:
Nested Loop
-> SeqScan tbl1
-> Append
-> Index Scan p01
-> Index Scan p02
-> Index Scan p03

For each tuple from tbl1, only the relevant partition (p01or p02 or
p03) will be scanned.

--- Prepared Statement Behaviour with patch---

Table Descritpion:
Table "public.tprt"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
col1 | integer | | | | plain | |
col2 | integer | | | | plain | |
Partition key: RANGE (col1)
Partitions: tprt_1 FOR VALUES FROM (1) TO (50001),
tprt_2 FOR VALUES FROM (50001) TO (100001),
tprt_3 FOR VALUES FROM (100001) TO (200001)

EXPLAIN EXECUTE prstmt_select(15);

QUERY PLAN
------------------------------------------------------------------
Append (cost=0.00..1736.55 rows=1 width=8)
-> Seq Scan on tprt_1 (cost=0.00..849.15 rows=16724 width=8)
Filter: (col1 < $1)
(3 rows)

EXPLAIN EXECUTE prstmt_select(60000);
QUERY PLAN
------------------------------------------------------------------
Append (cost=0.00..1736.55 rows=2 width=8)
-> Seq Scan on tprt_1 (cost=0.00..849.15 rows=16724 width=8)
Filter: (col1 < $1)
-> Seq Scan on tprt_2 (cost=0.00..849.15 rows=16724 width=8)
Filter: (col1 < $1)
(5 rows)

--

Beena Emerson

EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company