Horton

Re: Runtime Partition Pruning

Beena Emerson <memissemerson@gmail.com>

View thread View on postgr.es
From: Beena Emerson <memissemerson@gmail.com>
To: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Date: 2017-11-09T11:18:36Z
Lists: pgsql-hackers

Attachments

Hello all,

Here is the updated patch which is rebased over v10 of Amit Langote's
path towards faster pruning patch [1]. It modifies the PartScanKeyInfo
struct to hold expressions which is then evaluated by the executor to
fetch the correct partitions using the function.

The code still chooses the custom plan instead of the generic plan for
the prepared statements. I am working on it. The following output is
after adding a hack in the code forcing selection of generic plan.

postgres=# EXPLAIN EXECUTE prstmt_select(70000);
                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..1732.25 rows=2 width=8)
   ->  Seq Scan on tprt_1  (cost=0.00..847.00 rows=16667 width=8)
         Filter: ($1 > col1)
   ->  Seq Scan on tprt_2  (cost=0.00..847.00 rows=16667 width=8)
         Filter: ($1 > col1)
(5 rows)

postgres=# EXPLAIN EXECUTE prstmt_select(200000);
                            QUERY PLAN
------------------------------------------------------------------
 Append  (cost=0.00..1732.25 rows=3 width=8)
   ->  Seq Scan on tprt_1  (cost=0.00..847.00 rows=16667 width=8)
         Filter: ($1 > col1)
   ->  Seq Scan on tprt_2  (cost=0.00..847.00 rows=16667 width=8)
         Filter: ($1 > col1)
   ->  Seq Scan on tprt_3  (cost=0.00..38.25 rows=753 width=8)
         Filter: ($1 > col1)
(7 rows)


[1] https://www.postgresql.org/message-id/b8094e71-2c73-ed8e-d8c3-53f232c8c049%40lab.ntt.co.jp

Tested on commit: 9b9cb3c4534d717c1c95758670198ebbf8a20af2

-- 

Beena Emerson

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

Commits

  1. Initialize ExprStates once in run-time partition pruning

  2. Add bms_prev_member function

  3. Support partition pruning at execution time

  4. Document partprune.c a little better

  5. Fix crash in pg_replication_slot_advance

  6. Add parallel-aware hash joins.

  7. Fix typo.

  8. Allow --with-bonjour to work with non-macOS implementations of Bonjour.

  9. Mention need for --no-inc-recursive in rsync command