rowcount estimate varies WRT partitionwise_join

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: rowcount estimate varies WRT partitionwise_join
Date: 2018-10-14 15:09:15
Message-ID: 20181014150915.GB10792@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-10-14 16:16:49 Re: [HACKERS] removing abstime, reltime, tinterval.c, spi/timetravel
Previous Message Andrew Dunstan 2018-10-14 14:24:55 Re: pgsql: Add TAP tests for pg_verify_checksums