Re: Asymmetric partition-wise JOIN

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