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 по дате отправления:

Предыдущее
От: Andrew Dunstan
Дата:
Сообщение: Re: pgsql: Add TAP tests for pg_verify_checksums
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] removing abstime, reltime, tinterval.c, spi/timetravel