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

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