Re: BUG #15430: partition-wise join only works in combination withpruning on 1 partition - Mailing list pgsql-bugs
| From | Bart D |
|---|---|
| Subject | Re: BUG #15430: partition-wise join only works in combination withpruning on 1 partition |
| Date | |
| Msg-id | 20BCBC3B-6F69-46C4-8EB1-FCF77BBF9FF1@gmail.com Whole thread Raw |
| In response to | Re: BUG #15430: partition-wise join only works in combination withpruning on 1 partition (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| Responses |
Re: BUG #15430: partition-wise join only works in combination withpruning on 1 partition
|
| List | pgsql-bugs |
Amit,
You are right, I think the feature should be enabled by default though.
This feature guarantees linear scalability.
My apologies, please close the bug.
Please find results below.
Thanks in advance,
psql <<-EOF
show enable_partitionwise_join;
set enable_partitionwise_join = on;
show enable_partitionwise_join;
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
EOF
# Append (cost=257.79..573.65 rows=172 width=32)
# -> Merge Join (cost=257.79..286.40 rows=86 width=32)
# Merge Cond: ((m1.city_id = m2.city_id) AND (m1.logdate = m2.logdate))
# -> Sort (cost=128.89..133.52 rows=1850 width=16)
# Sort Key: m1.city_id, m1.logdate
# -> Seq Scan on measurement_y2006m02 m1 (cost=0.00..28.50 rows=1850 width=16)
# -> Sort (cost=128.89..133.52 rows=1850 width=16)
# Sort Key: m2.city_id, m2.logdate
# -> Seq Scan on measurement2_y2006m02 m2 (cost=0.00..28.50 rows=1850 width=16)
# -> Merge Join (cost=257.79..286.40 rows=86 width=32)
# Merge Cond: ((m1_1.city_id = m2_1.city_id) AND (m1_1.logdate = m2_1.logdate))
# -> Sort (cost=128.89..133.52 rows=1850 width=16)
# Sort Key: m1_1.city_id, m1_1.logdate
# -> Seq Scan on measurement_y2006m03 m1_1 (cost=0.00..28.50 rows=1850 width=16)
# -> Sort (cost=128.89..133.52 rows=1850 width=16)
# Sort Key: m2_1.city_id, m2_1.logdate
# -> Seq Scan on measurement2_y2006m03 m2_1 (cost=0.00..28.50 rows=1850 width=16)
psql <<-EOF
show enable_partitionwise_join;
set enable_partitionwise_join = on;
show enable_partitionwise_join;
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
EOF
# Append (cost=33.40..160.82 rows=2 width=32)
# -> Hash Join (cost=33.40..80.41 rows=1 width=32)
# Hash Cond: ((m2.city_id = m1.city_id) AND (m2.logdate = m1.logdate))
# -> Seq Scan on measurement2_y2006m02 m2 (cost=0.00..28.50 rows=1850 width=16)
# -> Hash (cost=33.12..33.12 rows=18 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[]))
# -> Hash Join (cost=33.40..80.41 rows=1 width=32)
# Hash Cond: ((m2_1.city_id = m1_1.city_id) AND (m2_1.logdate = m1_1.logdate))
# -> Seq Scan on measurement2_y2006m03 m2_1 (cost=0.00..28.50 rows=1850 width=16)
# -> Hash (cost=33.12..33.12 rows=18 width=16)
# -> 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[]))
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 <<-EOFEOF
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)# 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
pgsql-bugs by date: