Re: [HACKERS] Partition-wise aggregation/grouping - Mailing list pgsql-hackers

On Tue, Nov 28, 2017 at 12:37 PM, Rajkumar Raghuwanshi < rajkumar.raghuwanshi@enterprisedb.com> wrote: > On Thu, Nov 23, 2017 at 6:38 PM, Jeevan Chalke > wrote: > > Let me know if I missed any comment to be fixed. > > Hi, > > I have applied v8 patches on commit id 8735978e7aebfbc499843630131c18 > d1f7346c79, > and getting below observation, please take a look. > > Observation: > "when joining a foreign partition table with local partition table > getting wrong output > with partition_wise_join enabled, same is working fine on PG-head > without aggregates patch." > I have observed the same behavior on the master branch too when partition-wise join path is selected irrespective of this patch-set. This is happening because data on the foreign table is not compliance with the partitioning constraints. > Test-case: > CREATE EXTENSION postgres_fdw; > CREATE SERVER pwj_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS > (dbname 'postgres',port '5432',use_remote_estimate 'true'); > CREATE USER MAPPING FOR PUBLIC SERVER pwj_server; > > CREATE TABLE fplt1 (a int, c text) PARTITION BY LIST(c); > CREATE TABLE fplt1_p1 (a int, c text); > CREATE TABLE fplt1_p2 (a int, c text); > CREATE FOREIGN TABLE ftplt1_p1 PARTITION OF fplt1 FOR VALUES IN > ('0000', '0001', '0002', '0003') SERVER pwj_server OPTIONS (TABLE_NAME > 'fplt1_p1'); > CREATE FOREIGN TABLE ftplt1_p2 PARTITION OF fplt1 FOR VALUES IN > ('0004', '0005', '0006', '0007') SERVER pwj_server OPTIONS (TABLE_NAME > 'fplt1_p2'); > INSERT INTO fplt1_p1 SELECT i, to_char(i%8, 'FM0000') FROM > generate_series(0, 199, 2) i; > INSERT INTO fplt1_p2 SELECT i, to_char(i%8, 'FM0000') FROM > generate_series(200, 398, 2) i; > > CREATE TABLE lplt2 (a int, c text) PARTITION BY LIST(c); > CREATE TABLE lplt2_p1 PARTITION OF lplt2 FOR VALUES IN ('0000', > '0001', '0002', '0003'); > CREATE TABLE lplt2_p2 PARTITION OF lplt2 FOR VALUES IN ('0004', > '0005', '0006', '0007'); > INSERT INTO lplt2 SELECT i, to_char(i%8, 'FM0000') FROM > generate_series(0, 398, 3) i; > > SELECT t1.c, t2.c,count(*) FROM fplt1 t1 JOIN lplt2 t2 ON (t1.c = t2.c > and t1.a = t2.a) WHERE t1.a % 25 = 0 GROUP BY 1,2 ORDER BY t1.c, > t2.c; > c | c | count > ------+------+------- > 0000 | 0000 | 1 > 0004 | 0004 | 1 > 0006 | 0006 | 1 > (3 rows) > > SET enable_partition_wise_join = on; > SELECT t1.c, t2.c,count(*) FROM fplt1 t1 JOIN lplt2 t2 ON (t1.c = t2.c > and t1.a = t2.a) WHERE t1.a % 25 = 0 GROUP BY 1,2 ORDER BY t1.c, > t2.c; > c | c | count > ------+------+------- > 0000 | 0000 | 1 > 0004 | 0004 | 1 > (2 rows) > > > Thanks & Regards, > Rajkumar Raghuwanshi > QMG, EnterpriseDB Corporation > -- Jeevan Chalke Technical Architect, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company