rowcount estimate varies WRT partitionwise_join
От | Justin Pryzby |
---|---|
Тема | rowcount estimate varies WRT partitionwise_join |
Дата | |
Msg-id | 20181014150915.GB10792@telsasoft.com обсуждение исходный текст |
Ответы |
Re: rowcount estimate varies WRT partitionwise_join
|
Список | pgsql-hackers |
I was crosseyed yesterday due to merge conflicts, but this still seems odd. I thought that final row counts would not vary with the details of the chosen plan. Which seems to hold true when I disable parallel join or hash join, but not for PWJ. I noticed this behavior while joining our own tables using eq join on the partition key plus an inequality comparison also on the partition key (range), but I see the same thing using tables from the regression test: pryzbyj=# EXPLAIN SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ; QUERY PLAN ------------------------------------------------------------------------------ Hash Join (cost=6.96..13.83 rows=12 width=18) Hash Cond: (t2.b = t1.a) -> Append (cost=0.00..6.00 rows=200 width=9) -> Seq Scan on prt2_p1 t2 (cost=0.00..1.84 rows=84 width=9) -> Seq Scan on prt2_p2 t2_1 (cost=0.00..1.83 rows=83 width=9) -> Seq Scan on prt2_p3 t2_2 (cost=0.00..1.33 rows=33 width=9) -> Hash (cost=6.81..6.81 rows=12 width=9) -> Append (cost=0.00..6.81 rows=12 width=9) -> Seq Scan on prt1_p1 t1 (cost=0.00..2.56 rows=5 width=9) Filter: (b = 0) -> Seq Scan on prt1_p2 t1_1 (cost=0.00..2.56 rows=5 width=9) Filter: (b = 0) -> Seq Scan on prt1_p3 t1_2 (cost=0.00..1.62 rows=2 width=9) Filter: (b = 0) pryzbyj=# SET enable_partitionwise_join=on; pryzbyj=# EXPLAIN SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ; QUERY PLAN ------------------------------------------------------------------------------ Append (cost=2.62..12.75 rows=7 width=18) -> Hash Join (cost=2.62..4.81 rows=3 width=18) Hash Cond: (t2.b = t1.a) -> Seq Scan on prt2_p1 t2 (cost=0.00..1.84 rows=84 width=9) -> Hash (cost=2.56..2.56 rows=5 width=9) -> Seq Scan on prt1_p1 t1 (cost=0.00..2.56 rows=5 width=9) Filter: (b = 0) -> Hash Join (cost=2.62..4.80 rows=3 width=18) Hash Cond: (t2_1.b = t1_1.a) -> Seq Scan on prt2_p2 t2_1 (cost=0.00..1.83 rows=83 width=9) -> Hash (cost=2.56..2.56 rows=5 width=9) -> Seq Scan on prt1_p2 t1_1 (cost=0.00..2.56 rows=5 width=9) Filter: (b = 0) -> Hash Join (cost=1.65..3.11 rows=1 width=18) Hash Cond: (t2_2.b = t1_2.a) -> Seq Scan on prt2_p3 t2_2 (cost=0.00..1.33 rows=33 width=9) -> Hash (cost=1.62..1.62 rows=2 width=9) -> Seq Scan on prt1_p3 t1_2 (cost=0.00..1.62 rows=2 width=9) Filter: (b = 0)
В списке pgsql-hackers по дате отправления: