suganthi Sekar wrote: > i am using Postgresql 11, i have 2 partition table , when i joined both table in query > a table its goes exact partition table , but other table scan all partition > > please clarify on this . > > i have enabled below parameter on in configuration file > Note : alter system set enable_partitionwise_join to 'on'; > > > Example : > > explain analyze > select * from call_report1 as a inner join call_report2 as b on a.call_id=b.call_id > where a.call_created_date ='2017-11-01' and '2017-11-30' > > > > "Hash Right Join (cost=8.19..50.47 rows=2 width=3635) (actual time=0.426..0.447 rows=7 loops=1)" > " Hash Cond: (b.call_id = a.call_id)" > " -> Append (cost=0.00..41.81 rows=121 width=2319) (actual time=0.040..0.170 rows=104 loops=1)" > " -> Seq Scan on call_report2 b (cost=0.00..0.00 rows=1 width=528) (actual time=0.010..0.010 rows=0 loops=1)" > " -> Seq Scan on call_report2_201803 b_1 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.029..0.031 rows=14 loops=1)" > " -> Seq Scan on call_report2_201711 b_2 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.014..0.015 rows=7 loops=1)" > " -> Seq Scan on call_report2_201712 b_3 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.047 rows=34 loops=1)" > " -> Seq Scan on call_report2_201801 b_4 (cost=0.00..10.30 rows=30 width=2334) (actual time=0.017..0.058 rows=49 loops=1)" > " -> Hash (cost=8.17..8.17 rows=2 width=1314) (actual time=0.104..0.104 rows=7 loops=1)" > " Buckets: 1024 Batches: 1 Memory Usage: 12kB" > " -> Append (cost=0.00..8.17 rows=2 width=1314) (actual time=0.053..0.060 rows=7 loops=1)" > " -> Seq Scan on call_report1 a (cost=0.00..0.00 rows=1 width=437) (actual time=0.022..0.022 rows=0 loops=1)" > " Filter: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))" > " -> Index Scan using idx_call_report1_201711_ccd on call_report1_201711 a_1 (cost=0.14..8.16 rows=1 width=2190) (actual time=0.029..0.034 rows=7 loops=1)" > " Index Cond: ((call_created_date >= '2017-11-01'::date) AND (call_created_date <= '2017-11-30'::date))" > "Planning Time: 20.866 ms" > "Execution Time: 1.205 ms" There is no condition on the table "call_report2" in your query, so it is not surprising that all partitions are scanned, right? You have to add a WHERE condition that filters on the partitioning column(s) of "call_report2". Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
