Re: BUG #15430: partition-wise join only works in combination with pruning 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(dot)data(dot)analytics(at)gmail(dot)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