Re: force partition pruning

ok,

partitioning - Can PostgreSQL 12 do partition pruning at execution time
with subquery returning a list? - Stack Overflow
<https://stackoverflow.com/questions/61111395/can-postgresql-12-do-partition-pruning-at-execution-time-with-subquery-returning>

ok forcing hash join off, did not work as the outer table was the
partitioned table selected.

On Tue, 11 May 2021 at 22:42, Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

> Ok. maybe you are in a rush.
>
> But I would keep the thread open, to understand what I am not
> understanding or else, it'll become a habit of converting sql to plpgsql :)
>
> Big Guys,
> It seems, when the table is partitioned by range, it makes use of a nested
> loop which helps in partition pruning.
> if the table is list partitioned, it scans all the partitions.
>
> Is this expected ?
>
>
>
> LIST BASED PARTITION
> **********************
> postgres(at)go:~$ more p.sql
> drop table tbl1;
> drop table tprt;
>
> create table tbl1(col1 int);
> insert into tbl1 values (501), (505);
>
> -- Basic table
> create table tprt (col1 int) partition by list (col1);
> create table tprt_1 partition of tprt for values in (501);
> create table tprt_2 partition of tprt for values in (1001);
> create table tprt_3 partition of tprt for values in (2001);
> create table tprt_4 partition of tprt for values in (3001);
> create table tprt_5 partition of tprt for values in (4001);
> create table tprt_6 partition of tprt for values in (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 (501), (1001), (2001), (3001), (4001), (5001),
> (501);
>
> alter table tbl1 add column col2 int default 0;
> update tbl1 set col2 =1 where col1 = 501;
>
> vacuum analyze tprt;
> vacuum analyze tbl1;
>
> explain analyze select * from tprt where tprt.col1 in (select tbl1.col1
> from tbl1 where tbl1.col2 in (1, 2) );
>
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------
> Hash Semi Join (cost=1.05..7.20 rows=2 width=4) (actual
> time=0.028..0.034 rows=2 loops=1)
> Hash Cond: (tprt.col1 = tbl1.col1)
> -> Append (cost=0.00..6.10 rows=7 width=4) (actual time=0.003..0.008
> rows=7 loops=1)
> -> Seq Scan on tprt_1 (cost=0.00..1.02 rows=2 width=4) (actual
> time=0.002..0.003 rows=2 loops=1)
> -> Seq Scan on tprt_2 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_3 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_4 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.000..0.000 rows=1 loops=1)
> -> Seq Scan on tprt_5 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_6 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.006..0.006
> rows=1 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
> -> Seq Scan on tbl1 (cost=0.00..1.02 rows=2 width=4) (actual
> time=0.003..0.003 rows=1 loops=1)
> Filter: (col2 = ANY ('{1,2}'::integer[]))
> Rows Removed by Filter: 1
> Planning Time: 0.237 ms
> Execution Time: 0.060 ms
>
>
> *even if i set hashjoin off*
>
> postgres=# set enable_hashjoin TO 0;
> SET
> postgres=# explain analyze select * from tprt where tprt.col1 in (select
> tbl1.col1 from tbl1 where tbl1.col2 in (1, 2) );
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------
> Nested Loop Semi Join (cost=0.00..7.34 rows=2 width=4) (actual
> time=0.013..0.023 rows=2 loops=1)
> Join Filter: (tprt.col1 = tbl1.col1)
> Rows Removed by Join Filter: 5
> -> Append (cost=0.00..6.10 rows=7 width=4) (actual time=0.004..0.010
> rows=7 loops=1)
> -> Seq Scan on tprt_1 (cost=0.00..1.02 rows=2 width=4) (actual
> time=0.003..0.003 rows=2 loops=1)
> -> Seq Scan on tprt_2 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_3 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_4 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_5 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Seq Scan on tprt_6 (cost=0.00..1.01 rows=1 width=4) (actual
> time=0.001..0.001 rows=1 loops=1)
> -> Materialize (cost=0.00..1.03 rows=2 width=4) (actual
> time=0.001..0.001 rows=1 loops=7)
> -> Seq Scan on tbl1 (cost=0.00..1.02 rows=2 width=4) (actual
> time=0.007..0.007 rows=1 loops=1)
> Filter: (col2 = ANY ('{1,2}'::integer[]))
> Rows Removed by Filter: 1
> Planning Time: 0.578 ms
> Execution Time: 0.038 ms
> (16 rows)
>
> **********************
>
>
>
> RANGE BASED PARTITION
> **********************
> postgres(at)go:~$ more q.sql
> drop table tbl1;
> drop table tprt;
> 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 (0) to (500);
> create table tprt_2 partition of tprt for values from (500) to (1000);
> create table tprt_3 partition of tprt for values from (1000) to (1500);
> create table tprt_4 partition of tprt for values from (1500) to (2000);
> create table tprt_5 partition of tprt for values from (2000) to (22500);
>
> 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);
>
> insert into tprt values (501), (1001), (2001), (3001), (4001), (5001),
> (501);
>
> vacuum analyze tbl1;
> vacuum analyze tprt;
>
> alter table tbl1 add column col2 int default 0;
> update tbl1 set col2 =1 where col1 = 501;
>
> 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=3.29..55.37 rows=2 width=4) (actual time=0.016..0.018
> rows=2 loops=1)
> -> Unique (cost=1.03..1.04 rows=2 width=4) (actual time=0.012..0.012
> rows=1 loops=1)
> -> Sort (cost=1.03..1.04 rows=2 width=4) (actual
> time=0.011..0.011 rows=1 loops=1)
> Sort Key: tbl1.col1
> Sort Method: quicksort Memory: 25kB
> -> Seq Scan on tbl1 (cost=0.00..1.02 rows=2 width=4)
> (actual time=0.004..0.005 rows=1 loops=1)
> Filter: (col2 = ANY ('{1,2}'::integer[]))
> Rows Removed by Filter: 1
> -> Append (cost=2.26..26.86 rows=30 width=4) (actual
> time=0.003..0.004 rows=2 loops=1)
> -> Bitmap Heap Scan on tprt_1 (cost=2.26..11.81 rows=13
> width=4) (never executed)
> Recheck Cond: (col1 = tbl1.col1)
> -> Bitmap Index Scan on tprt1_idx (cost=0.00..2.25
> rows=13 width=0) (never executed)
> Index Cond: (col1 = tbl1.col1)
> -> Seq Scan on tprt_2 (cost=0.00..1.02 rows=2 width=4) (actual
> time=0.001..0.002 rows=2 loops=1)
> Filter: (tbl1.col1 = col1)
> -> Seq Scan on tprt_3 (cost=0.00..1.01 rows=1 width=4) (never
> executed)
> Filter: (tbl1.col1 = col1)
> -> Bitmap Heap Scan on tprt_4 (cost=2.26..11.81 rows=13
> width=4) (never executed)
> Recheck Cond: (col1 = tbl1.col1)
> -> Bitmap Index Scan on tprt4_idx (cost=0.00..2.25
> rows=13 width=0) (never executed)
> Index Cond: (col1 = tbl1.col1)
> -> Seq Scan on tprt_5 (cost=0.00..1.05 rows=1 width=4) (never
> executed)
> Filter: (tbl1.col1 = col1)
> Planning Time: 0.214 ms
> Execution Time: 0.069 ms
> (25 rows)
>
> **********************
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Tue, 11 May 2021 at 17:44, Niels Jespersen <NJN(at)dst(dot)dk> wrote:
>
>> >
>>
>> >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.
>>
>>
>>
>> Thats quite ok. I think my plan now is to have a table returning function
>> that executes a query dynamically. The query has a where caluse that is
>> first constructed.
>>
>>
>>
>> Like this:
>>
>>
>>
>>
>>
>> return query execute format('select d.x, d.y from %1$I.%1$I d where
>> d.period_version = any(' || quote_literal(_periode_version_array)
>> ||'::text[])', register_in);
>>
>>
>>
>> Regards Niels
>>
>>
>>
>>
>>
>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>

--
Thanks,
Vijay
Mumbai, India