Thread: Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
From
Rajkumar Raghuwanshi
Date:
On Fri, Apr 21, 2017 at 7:59 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
Hi,
I have applied v18 patches and got a crash in m-way joins when partition ranges differ, below are steps to reproduce this.
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
INSERT INTO prt1 SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
ANALYZE prt1;
CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
ANALYZE prt4_n;
SET enable_partition_wise_join = on ;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt1 t3 WHERE t1.a = t2.a AND t2.a = t3.a;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
Here's an updated patch set
Hi,
I have applied v18 patches and got a crash in m-way joins when partition ranges differ, below are steps to reproduce this.
CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
INSERT INTO prt1 SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
ANALYZE prt1;
CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
ANALYZE prt4_n;
SET enable_partition_wise_join = on ;
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt1 t3 WHERE t1.a = t2.a AND t2.a = t3.a;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
Re: [HACKERS] Partition-wise join for join between (declaratively)partitioned tables
From
Ashutosh Bapat
Date:
On Mon, Apr 24, 2017 at 5:02 PM, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> wrote: > On Fri, Apr 21, 2017 at 7:59 PM, Ashutosh Bapat > <ashutosh.bapat@enterprisedb.com> wrote: >> >> Here's an updated patch set > > > Hi, > > I have applied v18 patches and got a crash in m-way joins when partition > ranges differ, below are steps to reproduce this. > > CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a); > CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250); > CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600); > CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500); > INSERT INTO prt1 SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, > 599, 2) i; > ANALYZE prt1; > > CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a); > CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300); > CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500); > CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600); > INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, > 599, 2) i; > ANALYZE prt4_n; > > SET enable_partition_wise_join = on ; > EXPLAIN (COSTS OFF) > SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt1 t3 WHERE t1.a = > t2.a AND t2.a = t3.a; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !> Thanks Rajkumar for the report. When two relations with same partition scheme but different partition bounds are joined, their join relation has partition scheme set, but not partition bounds since we do not have logic to merge such partition bounds. When this join relation is joined further with other relation with same partition scheme, the code assumed that the join relation had partition bounds set. So the corresponding assertion failed. Instead, we should treat this condition same as the case of joining relations with different partition bounds and not use partition wise join for this join. This case may be further improved in the next set of patches by trying to merge partition bounds so that partition-wise join can be applied. Here's set of patches which fixes the issue. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers