Re: BUG #15430: partition-wise join only works in combination withpruning on 1 partition

On 15 Oct 2018, at 06:18, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote:

Hi,

On 2018/10/15 0:20, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference:      15430
Logged by:          Bart Debersaques
Email address:      agile.data.analytics@gmail.com
PostgreSQL version: 11rc1
Operating system:   Centos 7
Description:        

[ ... ]

psql <<-EOF
explain
select *
from
part_test.measurement m1
inner join part_test.measurement2 m2
on m1.city_id = m2.city_id and m1.logdate = m2.logdate
where m1.logdate in ('2006-02-01'::date, '2006-03-01'::date)

EOF

#  Hash Join  (cost=66.97..170.25 rows=3 width=32)
#   Hash Cond: ((m2.city_id = m1.city_id) AND (m2.logdate = m1.logdate))
#   ->  Append  (cost=0.00..75.50 rows=3700 width=16)
#         ->  Seq Scan on measurement2_y2006m02 m2  (cost=0.00..28.50
rows=1850 width=16)
#         ->  Seq Scan on measurement2_y2006m03 m2_1  (cost=0.00..28.50
rows=1850 width=16)
#   ->  Hash  (cost=66.43..66.43 rows=36 width=16)
#         ->  Append  (cost=0.00..66.43 rows=36 width=16)
#               ->  Seq Scan on measurement_y2006m02 m1  (cost=0.00..33.12
rows=18 width=16)
#                     Filter: (logdate = ANY
('{2006-02-01,2006-03-01}'::date[]))
#               ->  Seq Scan on measurement_y2006m03 m1_1  (cost=0.00..33.12
rows=18 width=16)
#                     Filter: (logdate = ANY
('{2006-02-01,2006-03-01}'::date[]))

# conclusion: join performed on full tab scans, join not performed on a
per-partion basis

Have you changed the value of enable_partitionwise_join parameter to 'on'?
It's 'off' by default, so join is not performed on per-partition basis.

Thanks,
Amit