Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.0 \(3608.60.0.2.5\))
Subject: Re: [HACKERS] advanced partition matching algorithm for
 partition-wise join
From: Mark Dilger <mark.dilger@enterprisedb.com>
In-Reply-To: 
 <CAPmGK14gTKUL4Enq-sYX1dVkySOK7JVPuEfUDcrr5P+3NHVQGg@mail.gmail.com>
Date: Wed, 5 Feb 2020 10:55:32 -0800
Cc: amul sul <sulamul@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Thomas Munro <thomas.munro@gmail.com>,
 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>,
 Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>,
 Dmitry Dolgov <9erthalion6@gmail.com>,
 Antonin Houska <ah@cybertec.at>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <768B0ED5-F2AA-4CB8-8DA3-41E2148CF42A@enterprisedb.com>
References: 
 <CAPmGK16LsKXX=YYzc-PNiY6aaYApg1Gmkc6A14dnJsrBBmgd0g@mail.gmail.com>
 <34362628-acdb-26d5-8edb-b579c34cb1a3@gmail.com>
 <4F6838E1-0FE8-4B92-B126-809866A064DE@enterprisedb.com>
 <CAPmGK14H4UFcrDFDS-vviBptxD356YvnFZjdtR5cbELoK4_cKA@mail.gmail.com>
 <CAPmGK14gTKUL4Enq-sYX1dVkySOK7JVPuEfUDcrr5P+3NHVQGg@mail.gmail.com>
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Precedence: bulk


> On Feb 5, 2020, at 4:51 AM, Etsuro Fujita <etsuro.fujita@gmail.com> =
wrote:
>=20
> =
<v31-0001-Applying-Etsuro-Fujita-s-patches.patch><v31-0004-Consider-pruned=
-partitions.patch>


The patches apply and pass all tests.  A review of the patch vs. master =
looks reasonable.

The partition_join.sql test has multiple levels of partitioning, but =
when your patch extends that test with =E2=80=9Cadvanced partition-wise =
join=E2=80=9D, none of the tables for the new section have multiple =
levels.  I spent a little while reviewing the code and inventing =
multiple level partitioning tests for advanced partition-wise join and =
did not encounter any problems.  I don=E2=80=99t care whether you use =
this particular example, but do you want to have multiple level =
partitioning in the new test section?

CREATE TABLE alpha (a double precision, b double precision) PARTITION BY =
RANGE (a);
CREATE TABLE alpha_neg PARTITION OF alpha FOR VALUES FROM ('-Infinity') =
TO (0) PARTITION BY RANGE (b);
CREATE TABLE alpha_pos PARTITION OF alpha FOR VALUES FROM (0) TO =
('Infinity') PARTITION BY RANGE (b);
CREATE TABLE alpha_nan PARTITION OF alpha FOR VALUES FROM ('Infinity') =
TO ('NaN');
CREATE TABLE alpha_neg_neg PARTITION OF alpha_neg FOR VALUES FROM =
('-Infinity') TO (0);
CREATE TABLE alpha_neg_pos PARTITION OF alpha_neg FOR VALUES FROM (0) TO =
('Infinity');
CREATE TABLE alpha_neg_nan PARTITION OF alpha_neg FOR VALUES FROM =
('Infinity') TO ('NaN');
CREATE TABLE alpha_pos_neg PARTITION OF alpha_pos FOR VALUES FROM =
('-Infinity') TO (0);
CREATE TABLE alpha_pos_pos PARTITION OF alpha_pos FOR VALUES FROM (0) TO =
('Infinity');
CREATE TABLE alpha_pos_nan PARTITION OF alpha_pos FOR VALUES FROM =
('Infinity') TO ('NaN');
INSERT INTO alpha (a, b)
    (SELECT * FROM
        (VALUES (-1.0::float8), (0.0::float8), (1.0::float8), =
('Infinity'::float8)) a,
        (VALUES (-1.0::float8), (0.0::float8), (1.0::float8), =
('Infinity'::float8)) b
    );
ANALYZE alpha;
ANALYZE alpha_neg;
ANALYZE alpha_pos;
ANALYZE alpha_nan;
ANALYZE alpha_neg_neg;
ANALYZE alpha_neg_pos;
ANALYZE alpha_neg_nan;
ANALYZE alpha_pos_neg;
ANALYZE alpha_pos_pos;
ANALYZE alpha_pos_nan;
CREATE TABLE beta (a double precision, b double precision) PARTITION BY =
RANGE (a, b);
CREATE TABLE beta_lo PARTITION OF beta FOR VALUES FROM (-5, -5) TO (0, =
0);
CREATE TABLE beta_me PARTITION OF beta FOR VALUES FROM (0, 0) TO (0, 5);
CREATE TABLE beta_hi PARTITION OF beta FOR VALUES FROM (0, 5) TO (5, 5);
INSERT INTO beta (a, b)
    (SELECT * FROM
        (VALUES (-1.0::float8), (0.0::float8), (1.0::float8)) a,
        (VALUES (-1.0::float8), (0.0::float8), (1.0::float8)) b
    );
ANALYZE beta;
ANALYZE beta_lo;
ANALYZE beta_me;
ANALYZE beta_hi;
EXPLAIN SELECT * FROM alpha INNER JOIN beta ON (alpha.a =3D beta.a AND =
alpha.b =3D beta.b) WHERE alpha.a =3D 1 AND beta.b =3D 1;
                                  QUERY PLAN
=
--------------------------------------------------------------------------=
-----
 Nested Loop  (cost=3D0.00..2.11 rows=3D1 width=3D32)
   ->  Seq Scan on alpha_pos_pos alpha  (cost=3D0.00..1.06 rows=3D1 =
width=3D16)
         Filter: ((b =3D '1'::double precision) AND (a =3D '1'::double =
precision))
   ->  Seq Scan on beta_hi beta  (cost=3D0.00..1.04 rows=3D1 width=3D16)
         Filter: ((b =3D '1'::double precision) AND (a =3D '1'::double =
precision))
(5 rows)


=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company