Re: force partition pruning

[Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx>]

Sorry,

I made a major mistake. I somehow saw the period and period_version as the same.

so, yes partitions are not pruned here. So my suggestion makes no sense.

 

***********************

create table register(period_version text) partition by list ( period_version );

create table register_p1 partition of register for values in ('201712_1');

create table register_p2 partition of register for values in ('201812_1');

create table register_p3 partition of register for values in ('201912_1');

insert into register values ('201712_1'),  ('201812_1'), ('201912_1');

create table register_metadata( period_version text, period text );

insert into register_metadata values ('201912_1', '201912' );

insert into register_metadata values ('201812_1', '201812' );

insert into register_metadata values ('201712_1', '201712' );

postgres=# select * from register_metadata m where m.period between '201512' and '201712';

 period_version | period

----------------+--------

 201712_1       | 201712

(1 row)

postgres=# explain analyze select * from register where period_version in ('201712_1');

                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------

 Seq Scan on register_p1 register  (cost=0.00..27.00 rows=7 width=32) (actual time=0.006..0.007 rows=1 loops=1)

   Filter: (period_version = '201712_1'::text)

 Planning Time: 0.192 ms

 Execution Time: 0.016 ms

(4 rows)

explain analyze select * from register d join register_metadata m  using (period_version) where m.period between '201512' and '201712';  -- partition not pruned

                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=23.25..130.57 rows=82 width=64) (actual time=0.026..0.032 rows=1 loops=1)

   Hash Cond: (d.period_version = m.period_version)

   ->  Append  (cost=0.00..91.20 rows=4080 width=32) (actual time=0.004..0.009 rows=3 loops=1)

         ->  Seq Scan on register_p1 d_1  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.004 rows=1 loops=1)

         ->  Seq Scan on register_p2 d_2  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.002..0.002 rows=1 loops=1)

         ->  Seq Scan on register_p3 d_3  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.001..0.001 rows=1 loops=1)

   ->  Hash  (cost=23.20..23.20 rows=4 width=64) (actual time=0.011..0.012 rows=1 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 9kB

         ->  Seq Scan on register_metadata m  (cost=0.00..23.20 rows=4 width=64) (actual time=0.007..0.008 rows=1 loops=1)

               Filter: ((period >= '201512'::text) AND (period <= '201712'::text))

               Rows Removed by Filter: 2

 Planning Time: 0.208 ms

 Execution Time: 0.051 ms

***********************

from the docs,

PostgreSQL: Documentation: 13: 5.11. Table Partitioning

• During initialization of the query plan. Partition pruning can be performed here for parameter values which are known during the initialization phase of execution. Partitions which are pruned during this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE. It is possible to determine the number of partitions which were removed during this phase by observing the “Subplans Removed” property in the EXPLAIN output.

• During actual execution of the query plan. Partition pruning may also be performed here to remove partitions using values which are only known during actual query execution. This includes values from subqueries and values from execution-time parameters such as those from parameterized nested loop joins. Since the value of these parameters may change many times during the execution of the query, partition pruning is performed whenever one of the execution parameters being used by partition pruning changes. Determining if partitions were pruned during this phase requires careful inspection of the loops property in the EXPLAIN ANALYZE output. Subplans corresponding to different partitions may have different values for it depending on how many times each of them was pruned during execution. Some may be shown as (never executed) if they were pruned every time.

but from the tests in the source

postgres/partition_prune.sql at master · postgres/postgres (github.com)

if we make a little change to suit your case

-- Join

create table tbl1(col1 int);

insert into tbl1 values (501), (505);

-- Basic table

create table tprt (col1 int) partition by range (col1);

create table tprt_1 partition of tprt for values from (1) to (501);

create table tprt_2 partition of tprt for values from (501) to (1001);

create table tprt_3 partition of tprt for values from (1001) to (2001);

create table tprt_4 partition of tprt for values from (2001) to (3001);

create table tprt_5 partition of tprt for values from (3001) to (4001);

create table tprt_6 partition of tprt for values from (4001) to (5001);

create index tprt1_idx on tprt_1 (col1);

create index tprt2_idx on tprt_2 (col1);

create index tprt3_idx on tprt_3 (col1);

create index tprt4_idx on tprt_4 (col1);

create index tprt5_idx on tprt_5 (col1);

create index tprt6_idx on tprt_6 (col1);

insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);

-- make this similar to your query.

alter table tbl1 add column col2 int default 0;

update tbl1 set col2 =1 where col1 = 501;

table tbl1;

 col1 | col2

------+------

  501 |    1

  505 |    0

explain analyze select * from tprt where tprt.col1 in (select tbl1.col1 from tbl1 where tbl1.col2 in (1, 2) );

partition pruning does take place.

explain analyze select * from tprt where tprt.col1 in (select tbl1.col1 from tbl1 where tbl1.col2 in (1, 2) );

                                                             QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------

 Nested Loop  (cost=38.46..382.58 rows=1760 width=4) (actual time=0.026..0.029 rows=1 loops=1)

   ->  HashAggregate  (cost=38.31..38.53 rows=22 width=4) (actual time=0.012..0.013 rows=1 loops=1)

         Group Key: tbl1.col1

         Batches: 1  Memory Usage: 24kB

         ->  Seq Scan on tbl1  (cost=0.00..38.25 rows=23 width=4) (actual time=0.008..0.009 rows=1 loops=1)

               Filter: (col2 = ANY ('{1,2}'::integer[]))

               Rows Removed by Filter: 1

   ->  Append  (cost=0.15..14.86 rows=78 width=4) (actual time=0.011..0.013 rows=1 loops=1)

         ->  Index Only Scan using tprt1_idx on tprt_1  (cost=0.15..2.47 rows=13 width=4) (never executed)

               Index Cond: (col1 = tbl1.col1)

               Heap Fetches: 0

         ->  Index Only Scan using tprt2_idx on tprt_2  (cost=0.15..2.47 rows=13 width=4) (actual time=0.009..0.010 rows=1 loops=1)

               Index Cond: (col1 = tbl1.col1)

               Heap Fetches: 1

         ->  Index Only Scan using tprt3_idx on tprt_3  (cost=0.15..2.47 rows=13 width=4) (never executed)

               Index Cond: (col1 = tbl1.col1)

               Heap Fetches: 0

         ->  Index Only Scan using tprt4_idx on tprt_4  (cost=0.15..2.30 rows=13 width=4) (never executed)

               Index Cond: (col1 = tbl1.col1)

               Heap Fetches: 0

         ->  Index Only Scan using tprt5_idx on tprt_5  (cost=0.15..2.30 rows=13 width=4) (never executed)

               Index Cond: (col1 = tbl1.col1)

               Heap Fetches: 0

         ->  Index Only Scan using tprt6_idx on tprt_6  (cost=0.15..2.47 rows=13 width=4) (never executed)

               Index Cond: (col1 = tbl1.col1)

               Heap Fetches: 0

 Planning Time: 0.211 ms

 Execution Time: 0.083 ms

(28 rows)

So, in short , i do not know how pruning works during execution time.

I hope someone else might be able to help you.

Thanks,

Vijay

 

On Mon, 10 May 2021 at 18:17, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:

I do not know how to put this in words,

but see below when the predicate is explicitly applied to the main table with partition.

postgres=# \d+ prt1

                                   Partitioned table "public.prt1"

 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

 a      | integer           |           | not null |         | plain    |              |

 b      | integer           |           |          |         | plain    |              |

 c      | character varying |           |          |         | extended |              |

Partition key: RANGE (a)

Partitions: prt1_p1 FOR VALUES FROM (0) TO (250),

            prt1_p2 FOR VALUES FROM (250) TO (500),

            prt1_p3 FOR VALUES FROM (500) TO (600)

(failed reverse-i-search)`': ^C

postgres=# \d+ b

                                     Table "public.b"

 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description

--------+---------+-----------+----------+---------+---------+--------------+-------------

 id     | integer |           | not null |         | plain   |              |

Indexes:

    "b_id_idx" btree (id)

Access method: heap

postgres=# table b;

 id

-----

 200

 400

(2 rows)

-- basically if the table is joined and predicate can be applied to the outer table which has constraints matching,

partition pruning takes place.

I do not know the theory, or even what i did is correct, but just FYI.

postgres=# explain analyze  select prt1.* from prt1 where a in ( select id from b where id in (1, 100, 200) );

                                                       QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------

 Hash Semi Join  (cost=1.05..9.36 rows=2 width=13) (actual time=0.034..0.074 rows=1 loops=1)

   Hash Cond: (prt1.a = b.id)

   ->  Append  (cost=0.00..7.50 rows=300 width=13) (actual time=0.006..0.043 rows=300 loops=1)

         ->  Seq Scan on prt1_p1 prt1_1  (cost=0.00..2.25 rows=125 width=13) (actual time=0.005..0.013 rows=125 loops=1)

         ->  Seq Scan on prt1_p2 prt1_2  (cost=0.00..2.25 rows=125 width=13) (actual time=0.003..0.009 rows=125 loops=1)

         ->  Seq Scan on prt1_p3 prt1_3  (cost=0.00..1.50 rows=50 width=13) (actual time=0.002..0.004 rows=50 loops=1)

   ->  Hash  (cost=1.03..1.03 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 9kB

         ->  Seq Scan on b  (cost=0.00..1.03 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=1)

               Filter: (id = ANY ('{1,100,200}'::integer[]))

               Rows Removed by Filter: 1

 Planning Time: 0.181 ms

 Execution Time: 0.089 ms

(13 rows)

postgres=# explain analyze  select prt1.* from prt1 where a in ( select id from b where b.id = prt1.a) and a in (1, 100, 200);

                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------

 Index Scan using iprt1_p1_a on prt1_p1 prt1  (cost=0.14..14.03 rows=2 width=13) (actual time=0.024..0.025 rows=1 loops=1)

   Index Cond: (a = ANY ('{1,100,200}'::integer[]))

   Filter: (SubPlan 1)

   Rows Removed by Filter: 1

   SubPlan 1

     ->  Seq Scan on b  (cost=0.00..1.02 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2)

           Filter: (id = prt1.a)

           Rows Removed by Filter: 1

 Planning Time: 0.120 ms

 Execution Time: 0.041 ms

(10 rows)

postgres=# explain analyze  select prt1.* from prt1 where exists ( select 1 from b where b.id = prt1.a) and a in (1, 100, 200);

                                                 QUERY PLAN

-------------------------------------------------------------------------------------------------------------

 Hash Semi Join  (cost=1.04..3.79 rows=2 width=13) (actual time=0.024..0.028 rows=1 loops=1)

   Hash Cond: (prt1.a = b.id)

   ->  Seq Scan on prt1_p1 prt1  (cost=0.00..2.72 rows=3 width=13) (actual time=0.011..0.017 rows=2 loops=1)

         Filter: (a = ANY ('{1,100,200}'::integer[]))

         Rows Removed by Filter: 123

   ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 9kB

         ->  Seq Scan on b  (cost=0.00..1.02 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)

 Planning Time: 0.192 ms

 Execution Time: 0.043 ms

(10 rows)

postgres=# explain analyze  select prt1.* from prt1 inner join b on prt1.a = b.id  where a in (1, 100, 200);

                                                 QUERY PLAN

-------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=1.04..3.79 rows=2 width=13) (actual time=0.024..0.028 rows=1 loops=1)

   Hash Cond: (prt1.a = b.id)

   ->  Seq Scan on prt1_p1 prt1  (cost=0.00..2.72 rows=3 width=13) (actual time=0.012..0.018 rows=2 loops=1)

         Filter: (a = ANY ('{1,100,200}'::integer[]))

         Rows Removed by Filter: 123

   ->  Hash  (cost=1.02..1.02 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)

         Buckets: 1024  Batches: 1  Memory Usage: 9kB

         ->  Seq Scan on b  (cost=0.00..1.02 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)

 Planning Time: 0.181 ms

 Execution Time: 0.043 ms

(10 rows)

On Mon, 10 May 2021 at 17:09, Niels Jespersen <NJN@xxxxxx> wrote:

Hi all

 

I need a litte advice on how to

 

Postgres 13.2

 

A metadata query pulls partition keys:

 

select m.period_version from register.register_metadata m where m.current_version and m.period between '201712' and '201912' ;

 

A query using these in an in-list easily makes the planner do partition pruning.

 

select * from register.register d where d.period_version in ('201712_1', '201812_1', '201912_1');

 

However combining the metadataquery into the dataquery makes the planner decide to scan all partitions.

 

select * from register.register d where d.period_version in (select m.period_version from register.register_metadata m where m.current_version and m.period between '201712' and '201912');

 

I am quite aware that the latter query requires partition pruning to take place during execution not during planning.

 

My question here is how do I package the two-step proces into an interface that analysts can actually use?

 

One possibility is to have a prepare step that creates a temporary view with the hard-coded values built-in. And then query data via the temp view. This works ok, but there is an issue with possible naming conflicts on the temp view (not that this could not be worked around).

 

Ideally I would like a function to figure out the query and then return the data from that dynamically executed query. Complicating matters is the fact that there are more than one set of data/metatable tables and each datatable has a different set of columns. This excludes a table returning function since that must list the columns present.

 

 

Best regards

 

 

Niels Jespersen

Chief Adviser

IT Center

Mobile phone:+45 42 42 93 73
Email: njn@xxxxxx

Statistics Denmark, Sejrøgade 11, DK-2100 Copenhagen

www.dst.dk/en | Twitter | LinkedIn | Facebook

 

 

 

 

 

 

--

Thanks,

Vijay

Mumbai, India

--

Thanks,

Vijay

Mumbai, India