Re: Asymmetric partition-wise JOIN

From: "Andrey V(dot) Lepikhov" <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Kohei KaiGai <kaigai(at)heterodb(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz>
Cc: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Asymmetric partition-wise JOIN
Date: 2020-07-06 08:46:23
Message-ID: 3dab799a-eeb2-dddc-69c5-7fe887ac7009@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 12/27/19 12:34 PM, Kohei KaiGai wrote:
> The attached v2 fixed the problem, and regression test finished correctly.
Using your patch I saw incorrect value of predicted rows at the top node
of the plan: "Append (cost=270.02..35165.37 rows=40004 width=16)"
Full explain of the query plan see in attachment -
explain_with_asymmetric.sql

if I disable enable_partitionwise_join then:
"Hash Join (cost=270.02..38855.25 rows=10001 width=16)"
Full explain - explain_no_asymmetric.sql

I thought that is the case of incorrect usage of cached values of
norm_selec, but it is a corner-case problem of the eqjoinsel() routine :

selectivity = 1/size_of_larger_relation; (selfuncs.c:2567)
tuples = selectivity * outer_tuples * inner_tuples; (costsize.c:4607)

i.e. number of tuples depends only on size of smaller relation.
It is not a bug of your patch but I think you need to know because it
may affect on planner decision.

===
P.S. Test case:
CREATE TABLE t0 (a serial, b int);
INSERT INTO t0 (b) (SELECT * FROM generate_series(1e4, 2e4) as g);
CREATE TABLE parts (a serial, b int) PARTITION BY HASH(a)
INSERT INTO parts (b) (SELECT * FROM generate_series(1, 1e6) as g);

--
regards,
Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
explain_with_asymmetric.sql application/sql 1.3 KB
explain_no_asymmetric.sql application/sql 612 bytes

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message vignesh C 2020-07-06 09:27:55 Re: Parallel copy
Previous Message Georgios Kokolatos 2020-07-06 08:34:56 Re: Using Valgrind to detect faulty buffer accesses (no pin or buffer content lock held)