Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hlr83-0004Yb-O5 for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Jul 2019 08:42:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hlr82-0002pV-Jz for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Jul 2019 08:42:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hlr81-0002kA-UZ for pgsql-hackers@lists.postgresql.org; Fri, 12 Jul 2019 08:42:06 +0000 Received: from mail-wr1-x444.google.com ([2a00:1450:4864:20::444]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hlr7x-0001QA-1t for pgsql-hackers@postgresql.org; Fri, 12 Jul 2019 08:42:05 +0000 Received: by mail-wr1-x444.google.com with SMTP id y4so9080626wrm.2 for ; Fri, 12 Jul 2019 01:42:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20150623.gappssmtp.com; s=20150623; h=from:to:cc:subject:in-reply-to:references:comments:mime-version :date:message-id; bh=kJgyUhiM1BAIpi3tGhx43Tp93N1F9qBXvk7KEu+18/I=; b=b4aqUdDMNVIKeM2qNU0gk5M6mYTwYKBS5dVanqLmQqR2vdxjy5Squ7xby9xdm0v4q6 O9+Ha/JPXlaxuhfmIr1eWFqtNZ5sEST8YPXI3AplVdKIyQjjVStMJzknlY304P8yosQQ BuEvX59LXqN92eewD/yXI7rgqX9LKfksvoBj/Mdv71rZRP9MovO0BIz7PpP0O+YEREfq vIL7PeFhSPT6ha14WLPrkL/3Zbn/RbhHys+GOgLehaBufnISkEETva5aqsiZV0PLL8qW quR/L5isrvPfw24yYNdm9KMd7IVyZToxlfCS83iRFTSHNDEXyf67mrwEsHhjHvyM5gN8 vH7w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:in-reply-to:references :comments:mime-version:date:message-id; bh=kJgyUhiM1BAIpi3tGhx43Tp93N1F9qBXvk7KEu+18/I=; b=GSlCraHzPmtaPld/RMDVgzDTm/NIpW+7OLv7viGPF7Yv+p5FU/z0u4JNRMYHFanvjv qmvQCqGHk7wdH/O3kR6Ny9cZSfLInZpwC5RyRmBhfrjFgenvpJz/fVYTCWYBHM4Z0UKR +KJ3oEuLDzjFe2HPczR/GiYBsxinHf5BOSGrcs148c+5O9hHE7QntlInWDEiRA1UO0Ne 5Hz5hLZgOeO9fx1BT07yNVnkX1BHfBvjmyxhc01tQOZJ8uGOAkdnFcaLdqjmBQMblMyg TtgwudXb+x9ViB/yE8m0pTifVyGplQuBt+iOmbwy3U7sMD644On+WGQqxrljPD/thrmt GCRw== X-Gm-Message-State: APjAAAXrJ6kIaSD/sO1S1eXhQtNLC6MWYewXY2jdpgL22+0WJ6E/PAij Ib2ErJCewSR0JA+N2CoUQrc= X-Google-Smtp-Source: APXvYqxUB1AvkAMdY5CqTU/4KfaheAPUbvHWICcpfJic6aYM6pSpyWLzGqCdnkYHeU0juOgiMaaSyw== X-Received: by 2002:adf:e843:: with SMTP id d3mr1357618wrn.249.1562920918893; Fri, 12 Jul 2019 01:41:58 -0700 (PDT) Received: from spoje.net (cst-prg-24-233.cust.vodafone.cz. [46.135.24.233]) by smtp.gmail.com with ESMTPSA id e5sm8161091wro.41.2019.07.12.01.41.56 (version=TLS1_2 cipher=ECDHE-RSA-CHACHA20-POLY1305 bits=256/256); Fri, 12 Jul 2019 01:41:57 -0700 (PDT) From: Antonin Houska To: Richard Guo cc: Tom Lane , Michael Paquier , PostgreSQL-development Subject: Re: [HACKERS] WIP: Aggregation push-down In-reply-to: References: <29613.1502983342@localhost> <14577.1509723225@localhost> <18007.1513957437@localhost> <31585.1519833763@localhost> <18061.1535727958@localhost> <9726.1542577439@sss.pgh.pa.us> <15879.1545062283@localhost> <10529.1547561178@localhost> <20190204060302.GC4092@paquier.xyz> <25767.1549359615@localhost> <11172.1550780283@sss.pgh.pa.us> <23468.1551369774@localhost> <1897.1562680064@spoje.net> Comments: In-reply-to Richard Guo message dated "Thu, 11 Jul 2019 18:27:46 +0800." X-Mailer: MH-E 8.6+git; nmh 1.7.1; GNU Emacs 26.1.50 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Date: Fri, 12 Jul 2019 10:41:51 +0200 Message-ID: <13971.1562920911@spoje.net> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 UmljaGFyZCBHdW8gPHJpZ3VvQHBpdm90YWwuaW8+IHdyb3RlOg0KDQo+IEkgZGlkbid0IGZ1bGx5 IGZvbGxvdyB0aGUgd2hvbGUgdGhyZWFkIGFuZCBtYWlubHkgbG9va2VkIGludG8gdGhlDQo+IGxh dGVzdA0KPiBwYXRjaCBzZXQuIFNvIHdoYXQgYXJlIHRoZSBjb25zaWRlcmF0aW9ucyBmb3IgYWJh bmRvbmluZyB0aGUNCj4gYWdnbXVsdGlmbg0KPiBjb25jZXB0Pw0KDQpPcmlnaW5hbGx5IHRoZSBm dW5jdGlvbiB3YXMgdGhlcmUgdG8gc3VwcG9ydCBqb2luIHdoZXJlIGJvdGggcmVsYXRpb25zIGFy ZQ0KZ3JvdXBlZC4gTXkgZG91YnRzIGFib3V0IHVzZWZ1bG5lc3Mgb2Ygc3VjaCBqb2luIHN0YXJ0 ZWQgYXQgWzFdLiAoU2VlIHRoZQ0KdGhyZWFkIHJlZmVyZW5jZWQgZnJvbSBbMl0uKQ0KDQo+IElu IG15IG9waW5pb24sIGFnZ211bHRpZm4gd291bGQgZW5hYmxlIHVzIHRvIGRvIGEgbG90IG1vcmUN Cj4gdHlwZXMgb2YgdHJhbnNmb3JtYXRpb24uIEZvciBleGFtcGxlLCBjb25zaWRlciB0aGUgcXVl cnkgYmVsb3c6DQo+IA0KPiBzZWxlY3Qgc3VtKGZvby5jKSBmcm9tIGZvbyBqb2luIGJhciBvbiBm b28uYiA9IGJhci5iIGdyb3VwIGJ5IGZvby5hLA0KPiBiYXIuYTsNCj4gDQo+IFdpdGggdGhlIGxh dGVzdCBwYXRjaCwgdGhlIHBsYW4gbG9va3MgbGlrZToNCj4gDQo+IEZpbmFsaXplIEhhc2hBZ2dy ZWdhdGUgwqAgwqA8LS0tLS0tIHN1bShwc3VtKQ0KPiDCoCDCoEdyb3VwIEtleTogZm9vLmEsIGJh ci5hDQo+IMKgIMKgLT4gwqBIYXNoIEpvaW4NCj4gwqAgwqAgwqAgwqAgwqBIYXNoIENvbmQ6IChi YXIuYiA9IGZvby5iKQ0KPiDCoCDCoCDCoCDCoCDCoC0+IMKgU2VxIFNjYW4gb24gYmFyDQo+IMKg IMKgIMKgIMKgIMKgLT4gwqBIYXNoDQo+IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgLT4gwqBQYXJ0 aWFsIEhhc2hBZ2dyZWdhdGUgwqAgwqA8LS0tLS0tIHN1bShmb28uYykgYXMNCj4gcHN1bQ0KPiDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoEdyb3VwIEtleTogZm9vLmEsIGZvby5iDQo+ IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgLT4gwqBTZXEgU2NhbiBvbiBmb28NCj4g DQo+IA0KPiBJZiB3ZSBoYXZlIGFnZ211bHRpZm4sIHdlIGNhbiBwZXJmb3JtIHRoZSBxdWVyeSB0 aGlzIHdheToNCj4gDQo+IEZpbmFsaXplIEhhc2hBZ2dyZWdhdGUgwqAgwqA8LS0tLS0tIHN1bShm b28uYykqY250DQo+IMKgIMKgR3JvdXAgS2V5OiBmb28uYSwgYmFyLmENCj4gwqAgwqAtPiDCoEhh c2ggSm9pbg0KPiDCoCDCoCDCoCDCoCDCoEhhc2ggQ29uZDogKGZvby5iID0gYmFyLmIpDQo+IMKg IMKgIMKgIMKgIMKgLT4gwqBTZXEgU2NhbiBvbiBmb28NCj4gwqAgwqAgwqAgwqAgwqAtPiDCoEhh c2gNCj4gwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAtPiDCoFBhcnRpYWwgSGFzaEFnZ3JlZ2F0ZSDC oCDCoDwtLS0tLS0gY291bnQoKikgYXMgY250DQo+IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKg IMKgIMKgR3JvdXAgS2V5OiBiYXIuYSwgYmFyLmINCj4gwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAg wqAgwqAgwqAtPiDCoFNlcSBTY2FuIG9uIGJhcg0KDQpQZXJoYXBzLCBidXQgaXQgdGhhdCB3b3Vs ZCByZXF1aXJlIGNoYW5nZXMgdG8gbm9kZUFnZy5jLCB3aGljaCBJIHdhbnQgdG8gYXZvaWQNCmZv ciBub3cuDQoNCj4gQW5kIHRoaXMgd2F5Og0KPiANCj4gRmluYWxpemUgSGFzaEFnZ3JlZ2F0ZSDC oCDCoDwtLS0tLS0gc3VtKHBzdW0pKmNudA0KPiDCoCDCoEdyb3VwIEtleTogZm9vLmEsIGJhci5h DQo+IMKgIMKgLT4gwqBIYXNoIEpvaW4NCj4gwqAgwqAgwqAgwqAgwqBIYXNoIENvbmQ6IChmb28u YiA9IGJhci5iKQ0KPiDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoC0+IMKgUGFydGlhbCBIYXNoQWdn cmVnYXRlIMKgIMKgPC0tLS0tLSBzdW0oZm9vLmMpIGFzDQo+IHBzdW0NCj4gwqAgwqAgwqAgwqAg wqAgwqAgwqAgwqAgwqAgwqAgwqBHcm91cCBLZXk6IGZvby5hLCBmb28uYg0KPiDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoCDCoCDCoCDCoC0+IMKgU2VxIFNjYW4gb24gZm9vDQo+IMKgIMKgIMKgIMKg IMKgLT4gwqBIYXNoDQo+IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgLT4gwqBQYXJ0aWFsIEhhc2hB Z2dyZWdhdGUgwqAgwqA8LS0tLS0tIGNvdW50KCopIGFzIGNudA0KPiDCoCDCoCDCoCDCoCDCoCDC oCDCoCDCoCDCoCDCoCDCoEdyb3VwIEtleTogYmFyLmEsIGJhci5iDQo+IMKgIMKgIMKgIMKgIMKg IMKgIMKgIMKgIMKgIMKgIMKgLT4gwqBTZXEgU2NhbiBvbiBiYXINCg0KVGhpcyBsb29rcyBsaWtl IG15IGlkZWEgcHJlc2VudGVkIGluIFsxXSwgZm9yIHdoaWNoIHRoZXJlIHNlZW1zIHRvIGJlIG5v DQpjb21tb24gdXNlIGNhc2UuDQoNCj4gTXkgYW5vdGhlciBxdWVzdGlvbiBpcyBpbiBmdW5jdGlv biBhZGRfZ3JvdXBlZF9wYXRoKCksIHdoZW4gY3JlYXRpbmcNCj4gc29ydGVkIGFnZ3JlZ2F0aW9u IHBhdGggb24gdG9wIG9mIHN1YnBhdGguIElmIHRoZSBzdWJwYXRoIGlzIG5vdA0KPiBzb3J0ZWQs DQo+IHRoZW4gdGhlIHNvcnRlZCBhZ2dyZWdhdGlvbiBwYXRoIHdvdWxkIG5vdCBiZSBnZW5lcmF0 ZWQuIFdoeSBub3QgaW4NCj4gdGhpcw0KPiBjYXNlIHdlIGNyZWF0ZSBhIHNvcnQgcGF0aCBvbiB0 b3Agb2Ygc3VicGF0aCBmaXJzdCBhbmQgdGhlbiBjcmVhdGUNCj4gZ3JvdXANCj4gYWdncmVnYXRp b24gcGF0aCBvbiB0b3Agb2YgdGhlIHNvcnQgcGF0aD8NCg0KSSBzZWUgbm8gcmVhc29uIG5vdCB0 byBkbyBpdC4gKElmIHlvdSB3YW50IHRvIHRyeSwganVzdCBnbyBhaGVhZC4pIEhvd2V2ZXIgdGhl DQpjdXJyZW50IHBhdGNoIHZlcnNpb24gYnJpbmdzIG9ubHkgdGhlIGJhc2ljIGZ1bmN0aW9uYWxp dHkgYW5kIEknbSBub3QgZ29pbmcgdG8NCmFkZCBuZXcgZnVuY3Rpb25hbGl0eSAoc3VjaCBhcyBw YXJhbGxlbCBhZ2dyZWdhdGlvbiwgcGFydGl0aW9uZWQgdGFibGVzIG9yDQpwb3N0Z3Jlc19mZHcp IHVudGlsIHRoZSBvcGVuIGRlc2lnbiBxdWVzdGlvbnMgYXJlIHJlc29sdmVkLiBPdGhlcndpc2Ug dGhlcmUncw0KYSByaXNrIHRoYXQgdGhlIGFkZGl0aW9uYWwgd29yayB3aWxsIGJlIHdhc3RlZCBk dWUgdG8gbWFqb3IgcmV3b3JrIG9mIHRoZSBjb3JlDQpmdW5jdGlvbmFsaXR5Lg0KDQo+IENvcmUg ZHVtcCB3aGVuIHJ1bm5pbmcgb25lIHF1ZXJ5IGluIGFnZ19wdXNoZG93bi5zcWwNCg0KVGhhbmtz IGZvciB0aGUgcmVwb3J0ISBGaXhlZCBpbiB0aGUgbmV3IHZlcnNpb24uDQoNCj4gVGhpcyBpcyBy ZWFsbHkgYSBjb29sIGZlYXR1cmUuIFRoYW5rIHlvdSBmb3Igd29ya2luZyBvbiB0aGlzLg0KDQpJ IGFwcHJlY2lhdGUgdG8gaGVhciB0aGF0IDotKSBMZXQncyBzZWUgd2hpY2ggcG9zdGdyZXMgcmVs ZWFzZSB3aWxsIGFkb3B0IGl0Lg0KDQpbMV0gaHR0cHM6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWVz c2FnZS1pZC9jYzgyM2U4OS0zZmJjLWY5NGUtYjlkNC05YzcxM2IwNDRiNWQlNDAybmRxdWFkcmFu dC5jb20NCg0KWzJdIGh0dHBzOi8vd3d3LnBvc3RncmVzcWwub3JnL21lc3NhZ2UtaWQvZmxhdC85 NjY2LjE0OTEyOTUzMTclNDBsb2NhbGhvc3QNCg0KLS0gDQpBbnRvbmluIEhvdXNrYQ0KV2ViOiBo dHRwczovL3d3dy5jeWJlcnRlYy1wb3N0Z3Jlc3FsLmNvbQ0KDQo= --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v13-0001-Introduce-RelInfoList-structure.patch From 5479312e35a0a6fa9b11318e97a626b15d98794c Mon Sep 17 00:00:00 2001 From: Antonin Houska Date: Fri, 12 Jul 2019 10:04:31 +0200 Subject: [PATCH 1/3] Introduce RelInfoList structure. --- contrib/postgres_fdw/postgres_fdw.c | 3 +- doc/src/sgml/catalogs.sgml | 5 + doc/src/sgml/ddl.sgml | 9 -- doc/src/sgml/func.sgml | 81 ++++++------- doc/src/sgml/json.sgml | 44 ++++---- src/backend/access/gist/gistbuildbuffers.c | 5 +- src/backend/commands/copy.c | 19 ++-- src/backend/commands/extension.c | 16 --- src/backend/commands/tablecmds.c | 23 +--- src/backend/commands/trigger.c | 1 + src/backend/executor/execMain.c | 1 - src/backend/nodes/outfuncs.c | 11 ++ src/backend/optimizer/geqo/geqo_eval.c | 12 +- src/backend/optimizer/plan/planmain.c | 3 +- src/backend/optimizer/util/relnode.c | 157 ++++++++++++++++---------- src/backend/partitioning/partprune.c | 50 +++----- src/backend/tcop/postgres.c | 30 +---- src/bin/initdb/initdb.c | 2 +- src/bin/pg_basebackup/pg_recvlogical.c | 5 +- src/bin/pg_checksums/pg_checksums.c | 4 +- src/bin/pg_dump/pg_backup_db.c | 4 +- src/bin/pg_dump/pg_dumpall.c | 6 +- src/bin/pg_upgrade/option.c | 2 +- src/include/access/tableam.h | 12 +- src/include/nodes/nodes.h | 1 + src/include/nodes/pathnodes.h | 28 +++-- src/test/regress/expected/partition_prune.out | 15 +-- src/test/regress/expected/triggers.out | 24 ---- src/test/regress/sql/partition_prune.sql | 11 +- src/test/regress/sql/triggers.sql | 23 ---- 30 files changed, 248 insertions(+), 359 deletions(-) diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 033aeb2556..90414f1168 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -5205,7 +5205,8 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, */ Assert(fpinfo->relation_index == 0); /* shouldn't be set yet */ fpinfo->relation_index = - list_length(root->parse->rtable) + list_length(root->join_rel_list); + list_length(root->parse->rtable) + + list_length(root->join_rel_list->items); return true; } diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 68ad5071ca..3428a7c0fa 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9995,6 +9995,11 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx that blanks out the password field. + + This view explicitly exposes the OID column of the underlying table, + since that is needed to do joins to other catalogs. + + <structname>pg_roles</structname> Columns diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 9301f0227d..ed2d9c60d5 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -4003,15 +4003,6 @@ ALTER INDEX measurement_city_id_logdate_key - - - Unique constraints on partitioned tables must include all the - partition key columns. This limitation exists because - PostgreSQL can only enforce - uniqueness in each partition individually. - - - BEFORE ROW triggers, if necessary, must be defined diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a25c122ac8..185a184daa 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11514,8 +11514,7 @@ table2-mapping from the JSON data, similar to XPath expressions used for SQL access to XML. In PostgreSQL, path expressions are implemented as the jsonpath - data type and can use any elements described in - . + data type, described in . JSON query functions and operators @@ -11562,7 +11561,7 @@ table2-mapping }, { "location": [ 47.706, 13.2635 ], "start time": "2018-10-14 10:39:21", - "HR": 135 + "HR": 130 } ] } } @@ -11614,33 +11613,23 @@ table2-mapping When defining the path, you can also use one or more - filter expressions that work similar to the - WHERE clause in SQL. A filter expression begins with - a question mark and provides a condition in parentheses: - - -? (condition) - - - - - Filter expressions must be specified right after the path evaluation step - to which they are applied. The result of this step is filtered to include - only those items that satisfy the provided condition. SQL/JSON defines - three-valued logic, so the condition can be true, false, + filter expressions, which work similar to + the WHERE clause in SQL. Each filter expression + can provide one or more filtering conditions that are applied + to the result of the path evaluation. Each filter expression must + be enclosed in parentheses and preceded by a question mark. + Filter expressions are evaluated from left to right and can be nested. + The @ variable denotes the current path evaluation + result to be filtered, and can be followed by one or more accessor + operators to define the JSON element by which to filter the result. + Functions and operators that can be used in the filtering condition + are listed in . + SQL/JSON defines three-valued logic, so the result of the filter + expression may be true, false, or unknown. The unknown value - plays the same role as SQL NULL and can be tested - for with the is unknown predicate. Further path + plays the same role as SQL NULL. Further path evaluation steps use only those items for which filter expressions - return true. - - - - Functions and operators that can be used in filter expressions are listed - in . The path - evaluation result to be filtered is denoted by the @ - variable. To refer to a JSON element stored at a lower nesting level, - add one or more accessor operators after @. + return true. @@ -11654,8 +11643,8 @@ table2-mapping To get the start time of segments with such values instead, you have to filter out irrelevant segments before returning the start time, so the - filter expression is applied to the previous step, and the path used - in the condition is different: + filter is applied to the previous step and the path in the filtering + condition is different: '$.track.segments[*] ? (@.HR > 130)."start time"' @@ -11680,9 +11669,9 @@ table2-mapping - You can also nest filter expressions within each other: + You can also nest filters within each other: -'$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()' +'$.track ? (@.segments[*] ? (@.HR > 130)).segments.size()' This expression returns the size of the track if it contains any segments with high heart rate values, or an empty sequence otherwise. @@ -11965,14 +11954,14 @@ table2-mapping Less-than operator [1, 2, 3] $[*] ? (@ < 2) - 1 + 1, 2 <= Less-than-or-equal-to operator [1, 2, 3] - $[*] ? (@ <= 2) - 1, 2 + $[*] ? (@ < 2) + 1 > @@ -11982,7 +11971,7 @@ table2-mapping 3 - >= + > Greater-than-or-equal-to operator [1, 2, 3] $[*] ? (@ >= 2) @@ -12272,7 +12261,7 @@ table2-mapping @? jsonpath - Does JSON path return any item for the specified JSON value? + Does JSON path returns any item for the specified JSON value? '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)' @@ -12300,8 +12289,8 @@ table2-mapping The @? and @@ operators suppress - the following errors: lacking object field or array element, unexpected - JSON item type, and numeric errors. + errors including: lacking object field or array element, unexpected JSON + item type and numeric errors. This behavior might be helpful while searching over JSON document collections of varying structure. @@ -13157,17 +13146,17 @@ table2-mapping jsonb_path_query, jsonb_path_query_array and jsonb_path_query_first functions have optional vars and silent - arguments. + argument. - If the vars argument is specified, it provides an - object containing named variables to be substituted into a - jsonpath expression. + When vars argument is specified, it constitutes an object + contained variables to be substituted into jsonpath + expression. - If the silent argument is specified and has the - true value, these functions suppress the same errors - as the @? and @@ operators. + When silent argument is specified and has + true value, the same errors are suppressed as it is in + the @? and @@ operators. diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 0d8e2c6de4..2aa98024ae 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -815,18 +815,21 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu .**{level} - .**{start_level to - end_level} + .**{lower_level to + upper_level} + + + .**{lower_level to + last} - Same as .**, but with a filter over nesting - levels of JSON hierarchy. Nesting levels are specified as integers. - Zero level corresponds to the current object. To access the lowest - nesting level, you can use the last keyword. - This is a PostgreSQL extension of - the SQL/JSON standard. + Same as .**, but with filter over nesting + level of JSON hierarchy. Levels are specified as integers. + Zero level corresponds to current object. This is a + PostgreSQL extension of the SQL/JSON + standard. @@ -838,22 +841,19 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu - Array element accessor. - subscript can be - given in two forms: index - or start_index to end_index. - The first form returns a single array element by its index. The second - form returns an array slice by the range of indexes, including the - elements that correspond to the provided - start_index and end_index. + Array element accessor. subscript + might be given in two forms: expr + or lower_expr to upper_expr. + The first form specifies single array element by its index. The second + form specified array slice by the range of indexes. Zero index + corresponds to the first array element. - The specified index can be an integer, as - well as an expression returning a single numeric value, which is - automatically cast to integer. Zero index corresponds to the first - array element. You can also use the last keyword - to denote the last array element, which is useful for handling arrays - of unknown length. + An expression in the subscript may be an integer, + numeric expression, or any other jsonpath expression + returning single numeric value. The last keyword + can be used in the expression denoting the last subscript in an array. + That's helpful for handling arrays of unknown length. diff --git a/src/backend/access/gist/gistbuildbuffers.c b/src/backend/access/gist/gistbuildbuffers.c index 38f786848d..d71354140e 100644 --- a/src/backend/access/gist/gistbuildbuffers.c +++ b/src/backend/access/gist/gistbuildbuffers.c @@ -138,7 +138,6 @@ gistGetNodeBuffer(GISTBuildBuffers *gfbb, GISTSTATE *giststate, nodeBuffer->pageBlocknum = InvalidBlockNumber; nodeBuffer->pageBuffer = NULL; nodeBuffer->queuedForEmptying = false; - nodeBuffer->isTemp = false; nodeBuffer->level = level; /* @@ -187,8 +186,8 @@ gistAllocateNewPageBuffer(GISTBuildBuffers *gfbb) { GISTNodeBufferPage *pageBuffer; - pageBuffer = (GISTNodeBufferPage *) MemoryContextAllocZero(gfbb->context, - BLCKSZ); + pageBuffer = (GISTNodeBufferPage *) MemoryContextAlloc(gfbb->context, + BLCKSZ); pageBuffer->prev = InvalidBlockNumber; /* Set page free space */ diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 4f04d122c3..f1161f0fee 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -2518,8 +2518,7 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo, * The buffer must be flushed before cleanup. */ static inline void -CopyMultiInsertBufferCleanup(CopyMultiInsertInfo *miinfo, - CopyMultiInsertBuffer *buffer) +CopyMultiInsertBufferCleanup(CopyMultiInsertBuffer *buffer) { int i; @@ -2535,9 +2534,6 @@ CopyMultiInsertBufferCleanup(CopyMultiInsertInfo *miinfo, for (i = 0; i < MAX_BUFFERED_TUPLES && buffer->slots[i] != NULL; i++) ExecDropSingleTupleTableSlot(buffer->slots[i]); - table_finish_bulk_insert(buffer->resultRelInfo->ri_RelationDesc, - miinfo->ti_options); - pfree(buffer); } @@ -2589,7 +2585,7 @@ CopyMultiInsertInfoFlush(CopyMultiInsertInfo *miinfo, ResultRelInfo *curr_rri) buffer = (CopyMultiInsertBuffer *) linitial(miinfo->multiInsertBuffers); } - CopyMultiInsertBufferCleanup(miinfo, buffer); + CopyMultiInsertBufferCleanup(buffer); miinfo->multiInsertBuffers = list_delete_first(miinfo->multiInsertBuffers); } } @@ -2603,7 +2599,7 @@ CopyMultiInsertInfoCleanup(CopyMultiInsertInfo *miinfo) ListCell *lc; foreach(lc, miinfo->multiInsertBuffers) - CopyMultiInsertBufferCleanup(miinfo, lfirst(lc)); + CopyMultiInsertBufferCleanup(lfirst(lc)); list_free(miinfo->multiInsertBuffers); } @@ -3325,6 +3321,9 @@ CopyFrom(CopyState cstate) { if (!CopyMultiInsertInfoIsEmpty(&multiInsertInfo)) CopyMultiInsertInfoFlush(&multiInsertInfo, NULL); + + /* Tear down the multi-insert buffer data */ + CopyMultiInsertInfoCleanup(&multiInsertInfo); } /* Done, clean up */ @@ -3356,10 +3355,6 @@ CopyFrom(CopyState cstate) target_resultRelInfo->ri_FdwRoutine->EndForeignInsert(estate, target_resultRelInfo); - /* Tear down the multi-insert buffer data */ - if (insertMethod != CIM_SINGLE) - CopyMultiInsertInfoCleanup(&multiInsertInfo); - ExecCloseIndices(target_resultRelInfo); /* Close all the partitioned tables, leaf partitions, and their indices */ @@ -3371,6 +3366,8 @@ CopyFrom(CopyState cstate) FreeExecutorState(estate); + table_finish_bulk_insert(cstate->rel, ti_options); + return processed; } diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c index f7202cc9e7..59ca5cd5a9 100644 --- a/src/backend/commands/extension.c +++ b/src/backend/commands/extension.c @@ -717,21 +717,9 @@ execute_sql_string(const char *sql) foreach(lc1, raw_parsetree_list) { RawStmt *parsetree = lfirst_node(RawStmt, lc1); - MemoryContext per_parsetree_context, - oldcontext; List *stmt_list; ListCell *lc2; - /* - * We do the work for each parsetree in a short-lived context, to - * limit the memory used when there are many commands in the string. - */ - per_parsetree_context = - AllocSetContextCreate(CurrentMemoryContext, - "execute_sql_string per-statement context", - ALLOCSET_DEFAULT_SIZES); - oldcontext = MemoryContextSwitchTo(per_parsetree_context); - /* Be sure parser can see any DDL done so far */ CommandCounterIncrement(); @@ -784,10 +772,6 @@ execute_sql_string(const char *sql) PopActiveSnapshot(); } - - /* Clean up per-parsetree context. */ - MemoryContextSwitchTo(oldcontext); - MemoryContextDelete(per_parsetree_context); } /* Be sure to advance the command counter after the last script command */ diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0f1a9f0e54..3aee2d82ce 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -15915,7 +15915,6 @@ CloneRowTriggersToPartition(Relation parent, Relation partition) Datum value; bool isnull; List *cols = NIL; - List *trigargs = NIL; MemoryContext oldcxt; /* @@ -15980,31 +15979,11 @@ CloneRowTriggersToPartition(Relation parent, Relation partition) } } - /* Reconstruct trigger arguments list. */ - if (trigForm->tgnargs > 0) - { - char *p; - - value = heap_getattr(tuple, Anum_pg_trigger_tgargs, - RelationGetDescr(pg_trigger), &isnull); - if (isnull) - elog(ERROR, "tgargs is null for trigger \"%s\" in partition \"%s\"", - NameStr(trigForm->tgname), RelationGetRelationName(partition)); - - p = (char *) VARDATA_ANY(DatumGetByteaPP(value)); - - for (int i = 0; i < trigForm->tgnargs; i++) - { - trigargs = lappend(trigargs, makeString(pstrdup(p))); - p += strlen(p) + 1; - } - } - trigStmt = makeNode(CreateTrigStmt); trigStmt->trigname = NameStr(trigForm->tgname); trigStmt->relation = NULL; trigStmt->funcname = NULL; /* passed separately */ - trigStmt->args = trigargs; + trigStmt->args = NULL; /* passed separately */ trigStmt->row = true; trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK; trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK; diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index ee878d70a9..316692b7c2 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -1172,6 +1172,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, */ childStmt = (CreateTrigStmt *) copyObject(stmt); childStmt->funcname = NIL; + childStmt->args = NIL; childStmt->whenClause = NULL; /* If there is a WHEN clause, create a modified copy of it */ diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 29e2681484..27f0345515 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -2793,7 +2793,6 @@ EvalPlanQualStart(EPQState *epqstate, EState *parentestate, Plan *planTree) estate->es_range_table_array = parentestate->es_range_table_array; estate->es_range_table_size = parentestate->es_range_table_size; estate->es_relations = parentestate->es_relations; - estate->es_queryEnv = parentestate->es_queryEnv; estate->es_rowmarks = parentestate->es_rowmarks; estate->es_plannedstmt = parentestate->es_plannedstmt; estate->es_junkFilter = parentestate->es_junkFilter; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 8400dd319e..4529b5c63b 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2278,6 +2278,14 @@ _outRelOptInfo(StringInfo str, const RelOptInfo *node) WRITE_NODE_FIELD(partitioned_child_rels); } +static void +_outRelInfoList(StringInfo str, const RelInfoList *node) +{ + WRITE_NODE_TYPE("RELOPTINFOLIST"); + + WRITE_NODE_FIELD(items); +} + static void _outIndexOptInfo(StringInfo str, const IndexOptInfo *node) { @@ -4052,6 +4060,9 @@ outNode(StringInfo str, const void *obj) case T_RelOptInfo: _outRelOptInfo(str, obj); break; + case T_RelInfoList: + _outRelInfoList(str, obj); + break; case T_IndexOptInfo: _outIndexOptInfo(str, obj); break; diff --git a/src/backend/optimizer/geqo/geqo_eval.c b/src/backend/optimizer/geqo/geqo_eval.c index 6c69c1c147..c69f3469ba 100644 --- a/src/backend/optimizer/geqo/geqo_eval.c +++ b/src/backend/optimizer/geqo/geqo_eval.c @@ -92,11 +92,11 @@ geqo_eval(PlannerInfo *root, Gene *tour, int num_gene) * * join_rel_level[] shouldn't be in use, so just Assert it isn't. */ - savelength = list_length(root->join_rel_list); - savehash = root->join_rel_hash; + savelength = list_length(root->join_rel_list->items); + savehash = root->join_rel_list->hash; Assert(root->join_rel_level == NULL); - root->join_rel_hash = NULL; + root->join_rel_list->hash = NULL; /* construct the best path for the given combination of relations */ joinrel = gimme_tree(root, tour, num_gene); @@ -121,9 +121,9 @@ geqo_eval(PlannerInfo *root, Gene *tour, int num_gene) * Restore join_rel_list to its former state, and put back original * hashtable if any. */ - root->join_rel_list = list_truncate(root->join_rel_list, - savelength); - root->join_rel_hash = savehash; + root->join_rel_list->items = list_truncate(root->join_rel_list->items, + savelength); + root->join_rel_list->hash = savehash; /* release all the memory acquired within gimme_tree */ MemoryContextSwitchTo(oldcxt); diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.c index 2dbf1db844..0b9999c8a6 100644 --- a/src/backend/optimizer/plan/planmain.c +++ b/src/backend/optimizer/plan/planmain.c @@ -65,8 +65,7 @@ query_planner(PlannerInfo *root, * NOTE: append_rel_list was set up by subquery_planner, so do not touch * here. */ - root->join_rel_list = NIL; - root->join_rel_hash = NULL; + root->join_rel_list = makeNode(RelInfoList); root->join_rel_level = NULL; root->join_cur_level = 0; root->canon_pathkeys = NIL; diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 6054bd2b53..c238dd6538 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -31,11 +31,11 @@ #include "utils/hsearch.h" -typedef struct JoinHashEntry +typedef struct RelInfoEntry { - Relids join_relids; /* hash key --- MUST BE FIRST */ - RelOptInfo *join_rel; -} JoinHashEntry; + Relids relids; /* hash key --- MUST BE FIRST */ + void *data; +} RelInfoEntry; static void build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *input_rel); @@ -375,11 +375,11 @@ find_base_rel(PlannerInfo *root, int relid) } /* - * build_join_rel_hash - * Construct the auxiliary hash table for join relations. + * build_rel_hash + * Construct the auxiliary hash table for relation specific data. */ static void -build_join_rel_hash(PlannerInfo *root) +build_rel_hash(RelInfoList *list) { HTAB *hashtab; HASHCTL hash_ctl; @@ -388,47 +388,50 @@ build_join_rel_hash(PlannerInfo *root) /* Create the hash table */ MemSet(&hash_ctl, 0, sizeof(hash_ctl)); hash_ctl.keysize = sizeof(Relids); - hash_ctl.entrysize = sizeof(JoinHashEntry); + hash_ctl.entrysize = sizeof(RelInfoEntry); hash_ctl.hash = bitmap_hash; hash_ctl.match = bitmap_match; hash_ctl.hcxt = CurrentMemoryContext; - hashtab = hash_create("JoinRelHashTable", + hashtab = hash_create("RelHashTable", 256L, &hash_ctl, HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_CONTEXT); /* Insert all the already-existing joinrels */ - foreach(l, root->join_rel_list) + foreach(l, list->items) { - RelOptInfo *rel = (RelOptInfo *) lfirst(l); - JoinHashEntry *hentry; + void *item = lfirst(l); + RelInfoEntry *hentry; bool found; + Relids relids; - hentry = (JoinHashEntry *) hash_search(hashtab, - &(rel->relids), - HASH_ENTER, - &found); + Assert(IsA(item, RelOptInfo)); + relids = ((RelOptInfo *) item)->relids; + + hentry = (RelInfoEntry *) hash_search(hashtab, + &relids, + HASH_ENTER, + &found); Assert(!found); - hentry->join_rel = rel; + hentry->data = item; } - root->join_rel_hash = hashtab; + list->hash = hashtab; } /* - * find_join_rel - * Returns relation entry corresponding to 'relids' (a set of RT indexes), - * or NULL if none exists. This is for join relations. + * find_rel_info + * Find a base or join relation entry. */ -RelOptInfo * -find_join_rel(PlannerInfo *root, Relids relids) +static void * +find_rel_info(RelInfoList *list, Relids relids) { /* * Switch to using hash lookup when list grows "too long". The threshold * is arbitrary and is known only here. */ - if (!root->join_rel_hash && list_length(root->join_rel_list) > 32) - build_join_rel_hash(root); + if (!list->hash && list_length(list->items) > 32) + build_rel_hash(list); /* * Use either hashtable lookup or linear search, as appropriate. @@ -438,34 +441,90 @@ find_join_rel(PlannerInfo *root, Relids relids) * so would force relids out of a register and thus probably slow down the * list-search case. */ - if (root->join_rel_hash) + if (list->hash) { Relids hashkey = relids; - JoinHashEntry *hentry; + RelInfoEntry *hentry; - hentry = (JoinHashEntry *) hash_search(root->join_rel_hash, - &hashkey, - HASH_FIND, - NULL); + hentry = (RelInfoEntry *) hash_search(list->hash, + &hashkey, + HASH_FIND, + NULL); if (hentry) - return hentry->join_rel; + return hentry->data; } else { ListCell *l; - foreach(l, root->join_rel_list) + foreach(l, list->items) { - RelOptInfo *rel = (RelOptInfo *) lfirst(l); + void *item = lfirst(l); + Relids item_relids; - if (bms_equal(rel->relids, relids)) - return rel; + Assert(IsA(item, RelOptInfo)); + item_relids = ((RelOptInfo *) item)->relids; + + if (bms_equal(item_relids, relids)) + return item; } } return NULL; } +/* + * find_join_rel + * Returns relation entry corresponding to 'relids' (a set of RT indexes), + * or NULL if none exists. This is for join relations. + */ +RelOptInfo * +find_join_rel(PlannerInfo *root, Relids relids) +{ + return (RelOptInfo *) find_rel_info(root->join_rel_list, relids); +} + +/* + * add_rel_info + * Add relation specific info to a list, and also add it to the auxiliary + * hashtable if there is one. + */ +static void +add_rel_info(RelInfoList *list, void *data) +{ + Assert(IsA(data, RelOptInfo)); + + /* GEQO requires us to append the new joinrel to the end of the list! */ + list->items = lappend(list->items, data); + + /* store it into the auxiliary hashtable if there is one. */ + if (list->hash) + { + Relids relids; + RelInfoEntry *hentry; + bool found; + + relids = ((RelOptInfo *) data)->relids; + hentry = (RelInfoEntry *) hash_search(list->hash, + &relids, + HASH_ENTER, + &found); + Assert(!found); + hentry->data = data; + } +} + +/* + * add_join_rel + * Add given join relation to the list of join relations in the given + * PlannerInfo. + */ +static void +add_join_rel(PlannerInfo *root, RelOptInfo *joinrel) +{ + add_rel_info(root->join_rel_list, joinrel); +} + /* * set_foreign_rel_properties * Set up foreign-join fields if outer and inner relation are foreign @@ -516,32 +575,6 @@ set_foreign_rel_properties(RelOptInfo *joinrel, RelOptInfo *outer_rel, } } -/* - * add_join_rel - * Add given join relation to the list of join relations in the given - * PlannerInfo. Also add it to the auxiliary hashtable if there is one. - */ -static void -add_join_rel(PlannerInfo *root, RelOptInfo *joinrel) -{ - /* GEQO requires us to append the new joinrel to the end of the list! */ - root->join_rel_list = lappend(root->join_rel_list, joinrel); - - /* store it into the auxiliary hashtable if there is one. */ - if (root->join_rel_hash) - { - JoinHashEntry *hentry; - bool found; - - hentry = (JoinHashEntry *) hash_search(root->join_rel_hash, - &(joinrel->relids), - HASH_ENTER, - &found); - Assert(!found); - hentry->join_rel = joinrel; - } -} - /* * build_join_rel * Returns relation entry corresponding to the union of two given rels, diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c index e71a21c0a7..5982af4de1 100644 --- a/src/backend/partitioning/partprune.c +++ b/src/backend/partitioning/partprune.c @@ -194,10 +194,8 @@ static PruneStepResult *perform_pruning_base_step(PartitionPruneContext *context static PruneStepResult *perform_pruning_combine_step(PartitionPruneContext *context, PartitionPruneStepCombine *cstep, PruneStepResult **step_results); -static PartClauseMatchStatus match_boolean_partition_clause(Oid partopfamily, - Expr *clause, - Expr *partkey, - Expr **outconst); +static bool match_boolean_partition_clause(Oid partopfamily, Expr *clause, + Expr *partkey, Expr **outconst); static void partkey_datum_from_expr(PartitionPruneContext *context, Expr *expr, int stateidx, Datum *value, bool *isnull); @@ -1625,7 +1623,6 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context, bool *clause_is_not_null, PartClauseInfo **pc, List **clause_steps) { - PartClauseMatchStatus boolmatchstatus; PartitionScheme part_scheme = context->rel->part_scheme; Oid partopfamily = part_scheme->partopfamily[partkeyidx], partcoll = part_scheme->partcollation[partkeyidx]; @@ -1634,10 +1631,7 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context, /* * Recognize specially shaped clauses that match a Boolean partition key. */ - boolmatchstatus = match_boolean_partition_clause(partopfamily, clause, - partkey, &expr); - - if (boolmatchstatus == PARTCLAUSE_MATCH_CLAUSE) + if (match_boolean_partition_clause(partopfamily, clause, partkey, &expr)) { PartClauseInfo *partclause; @@ -2153,21 +2147,7 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context, return PARTCLAUSE_MATCH_NULLNESS; } - /* - * If we get here then the return value depends on the result of the - * match_boolean_partition_clause call above. If the call returned - * PARTCLAUSE_UNSUPPORTED then we're either not dealing with a bool qual - * or the bool qual is not suitable for pruning. Since the qual didn't - * match up to any of the other qual types supported here, then trying to - * match it against any other partition key is a waste of time, so just - * return PARTCLAUSE_UNSUPPORTED. If the qual just couldn't be matched to - * this partition key, then it may match another, so return - * PARTCLAUSE_NOMATCH. The only other value that - * match_boolean_partition_clause can return is PARTCLAUSE_MATCH_CLAUSE, - * and since that value was already dealt with above, then we can just - * return boolmatchstatus. - */ - return boolmatchstatus; + return PARTCLAUSE_UNSUPPORTED; } /* @@ -3415,15 +3395,11 @@ perform_pruning_combine_step(PartitionPruneContext *context, /* * match_boolean_partition_clause * - * If we're able to match the clause to the partition key as specially-shaped - * boolean clause, set *outconst to a Const containing a true or false value - * and return PARTCLAUSE_MATCH_CLAUSE. Returns PARTCLAUSE_UNSUPPORTED if the - * clause is not a boolean clause or if the boolean clause is unsuitable for - * partition pruning. Returns PARTCLAUSE_NOMATCH if it's a bool quals but - * just does not match this partition key. *outconst is set to NULL in the - * latter two cases. + * Sets *outconst to a Const containing true or false value and returns true if + * we're able to match the clause to the partition key as specially-shaped + * Boolean clause. Returns false otherwise with *outconst set to NULL. */ -static PartClauseMatchStatus +static bool match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey, Expr **outconst) { @@ -3432,7 +3408,7 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey, *outconst = NULL; if (!IsBooleanOpfamily(partopfamily)) - return PARTCLAUSE_UNSUPPORTED; + return false; if (IsA(clause, BooleanTest)) { @@ -3441,7 +3417,7 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey, /* Only IS [NOT] TRUE/FALSE are any good to us */ if (btest->booltesttype == IS_UNKNOWN || btest->booltesttype == IS_NOT_UNKNOWN) - return PARTCLAUSE_UNSUPPORTED; + return false; leftop = btest->arg; if (IsA(leftop, RelabelType)) @@ -3454,7 +3430,7 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey, : (Expr *) makeBoolConst(false, false); if (*outconst) - return PARTCLAUSE_MATCH_CLAUSE; + return true; } else { @@ -3474,10 +3450,10 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey, *outconst = (Expr *) makeBoolConst(false, false); if (*outconst) - return PARTCLAUSE_MATCH_CLAUSE; + return true; } - return PARTCLAUSE_NOMATCH; + return false; } /* diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index ffd84d877c..44a59e1d4f 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -1070,7 +1070,6 @@ exec_simple_query(const char *query_string) bool snapshot_set = false; const char *commandTag; char completionTag[COMPLETION_TAG_BUFSIZE]; - MemoryContext per_parsetree_context = NULL; List *querytree_list, *plantree_list; Portal portal; @@ -1133,25 +1132,10 @@ exec_simple_query(const char *query_string) /* * OK to analyze, rewrite, and plan this query. * - * Switch to appropriate context for constructing query and plan trees - * (these can't be in the transaction context, as that will get reset - * when the command is COMMIT/ROLLBACK). If we have multiple - * parsetrees, we use a separate context for each one, so that we can - * free that memory before moving on to the next one. But for the - * last (or only) parsetree, just use MessageContext, which will be - * reset shortly after completion anyway. In event of an error, the - * per_parsetree_context will be deleted when MessageContext is reset. + * Switch to appropriate context for constructing querytrees (again, + * these must outlive the execution context). */ - if (lnext(parsetree_item) != NULL) - { - per_parsetree_context = - AllocSetContextCreate(MessageContext, - "per-parsetree message context", - ALLOCSET_DEFAULT_SIZES); - oldcontext = MemoryContextSwitchTo(per_parsetree_context); - } - else - oldcontext = MemoryContextSwitchTo(MessageContext); + oldcontext = MemoryContextSwitchTo(MessageContext); querytree_list = pg_analyze_and_rewrite(parsetree, query_string, NULL, 0, NULL); @@ -1176,8 +1160,8 @@ exec_simple_query(const char *query_string) /* * We don't have to copy anything into the portal, because everything - * we are passing here is in MessageContext or the - * per_parsetree_context, and so will outlive the portal anyway. + * we are passing here is in MessageContext, which will outlive the + * portal anyway. */ PortalDefineQuery(portal, NULL, @@ -1279,10 +1263,6 @@ exec_simple_query(const char *query_string) * aborted by error will not send an EndCommand report at all.) */ EndCommand(completionTag, dest); - - /* Now we may drop the per-parsetree context, if one was created. */ - if (per_parsetree_context) - MemoryContextDelete(per_parsetree_context); } /* end loop over parsetrees */ /* diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c index 04d77ad700..f1acbdfcf2 100644 --- a/src/bin/initdb/initdb.c +++ b/src/bin/initdb/initdb.c @@ -1051,7 +1051,7 @@ test_config_settings(void) else printf("%dkB\n", n_buffers * (BLCKSZ / 1024)); - printf(_("selecting default time zone ... ")); + printf(_("selecting default timezone ... ")); fflush(stdout); default_timezone = select_default_timezone(share_path); printf("%s\n", default_timezone ? default_timezone : "GMT"); diff --git a/src/bin/pg_basebackup/pg_recvlogical.c b/src/bin/pg_basebackup/pg_recvlogical.c index 90a3f41bbb..b029118bf6 100644 --- a/src/bin/pg_basebackup/pg_recvlogical.c +++ b/src/bin/pg_basebackup/pg_recvlogical.c @@ -1020,11 +1020,12 @@ prepareToTerminate(PGconn *conn, XLogRecPtr endpos, bool keepalive, XLogRecPtr l if (verbose) { if (keepalive) - pg_log_info("end position %X/%X reached by keepalive", + pg_log_info("endpos %X/%X reached by keepalive", (uint32) (endpos >> 32), (uint32) endpos); else - pg_log_info("end position %X/%X reached by WAL record at %X/%X", + pg_log_info("endpos %X/%X reached by record at %X/%X", (uint32) (endpos >> 32), (uint32) (endpos), (uint32) (lsn >> 32), (uint32) lsn); + } } diff --git a/src/bin/pg_checksums/pg_checksums.c b/src/bin/pg_checksums/pg_checksums.c index 8c00ec9a3b..b591fcc864 100644 --- a/src/bin/pg_checksums/pg_checksums.c +++ b/src/bin/pg_checksums/pg_checksums.c @@ -235,7 +235,7 @@ scan_file(const char *fn, BlockNumber segmentno) /* Write block with checksum */ if (write(f, buf.data, BLCKSZ) != BLCKSZ) { - pg_log_error("could not write block %u in file \"%s\": %m", + pg_log_error("could not update checksum of block %u in file \"%s\": %m", blockno, fn); exit(1); } @@ -469,7 +469,7 @@ main(int argc, char *argv[]) /* filenode checking only works in --check mode */ if (mode != PG_MODE_CHECK && only_filenode) { - pg_log_error("option -f/--filenode can only be used with --check"); + pg_log_error("--filenode option only possible with --check"); fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); diff --git a/src/bin/pg_dump/pg_backup_db.c b/src/bin/pg_dump/pg_backup_db.c index ee822c5249..401e0c8883 100644 --- a/src/bin/pg_dump/pg_backup_db.c +++ b/src/bin/pg_dump/pg_backup_db.c @@ -176,7 +176,7 @@ _connectDB(ArchiveHandle *AH, const char *reqdb, const char *requser) newConn = PQconnectdbParams(keywords, values, true); if (!newConn) - fatal("could not reconnect to database"); + fatal("failed to reconnect to database"); if (PQstatus(newConn) == CONNECTION_BAD) { @@ -287,7 +287,7 @@ ConnectDatabase(Archive *AHX, AH->connection = PQconnectdbParams(keywords, values, true); if (!AH->connection) - fatal("could not connect to database"); + fatal("failed to connect to database"); if (PQstatus(AH->connection) == CONNECTION_BAD && PQconnectionNeedsPassword(AH->connection) && diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 0981efcf5d..158c0c74b2 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -482,7 +482,7 @@ main(int argc, char *argv[]) OPF = fopen(filename, PG_BINARY_W); if (!OPF) { - pg_log_error("could not open output file \"%s\": %m", + pg_log_error("could not open the output file \"%s\": %m", filename); exit_nicely(1); } @@ -1492,11 +1492,11 @@ dumpDatabases(PGconn *conn) /* Skip any explicitly excluded database */ if (simple_string_list_member(&database_exclude_names, dbname)) { - pg_log_info("excluding database \"%s\"", dbname); + pg_log_info("excluding database \"%s\"...", dbname); continue; } - pg_log_info("dumping database \"%s\"", dbname); + pg_log_info("dumping database \"%s\"...", dbname); fprintf(OPF, "--\n-- Database \"%s\" dump\n--\n\n", dbname); diff --git a/src/bin/pg_upgrade/option.c b/src/bin/pg_upgrade/option.c index d76f27c9e8..73f395f2a3 100644 --- a/src/bin/pg_upgrade/option.c +++ b/src/bin/pg_upgrade/option.c @@ -304,7 +304,7 @@ usage(void) printf(_(" -p, --old-port=PORT old cluster port number (default %d)\n"), old_cluster.port); printf(_(" -P, --new-port=PORT new cluster port number (default %d)\n"), new_cluster.port); printf(_(" -r, --retain retain SQL and log files after success\n")); - printf(_(" -s, --socketdir=DIR socket directory to use (default current dir.)\n")); + printf(_(" -s, --socketdir=DIR socket directory to use (default CWD)\n")); printf(_(" -U, --username=NAME cluster superuser (default \"%s\")\n"), os_info.user); printf(_(" -v, --verbose enable verbose internal logging\n")); printf(_(" -V, --version display version information, then exit\n")); diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h index c2b0481e7e..7edfcf3ef9 100644 --- a/src/include/access/tableam.h +++ b/src/include/access/tableam.h @@ -434,8 +434,8 @@ typedef struct TableAmRoutine * * Note that only the subset of the relcache filled by * RelationBuildLocalRelation() can be relied upon and that the relation's - * catalog entries will either not yet exist (new relation), or will still - * reference the old relfilenode. + * catalog entries either will either not yet exist (new relation), or + * will still reference the old relfilenode. * * As output *freezeXid, *minmulti must be set to the values appropriate * for pg_class.{relfrozenxid, relminmxid}. For AMs that don't need those @@ -591,7 +591,7 @@ typedef struct TableAmRoutine * See table_relation_estimate_size(). * * While block oriented, it shouldn't be too hard for an AM that doesn't - * internally use blocks to convert into a usable representation. + * doesn't internally use blocks to convert into a usable representation. * * This differs from the relation_size callback by returning size * estimates (both relation size and tuple count) for planning purposes, @@ -967,7 +967,7 @@ table_index_fetch_end(struct IndexFetchTableData *scan) * * *all_dead, if all_dead is not NULL, will be set to true by * table_index_fetch_tuple() iff it is guaranteed that no backend needs to see - * that tuple. Index AMs can use that to avoid returning that tid in future + * that tuple. Index AMs can use that do avoid returning that tid in future * searches. * * The difference between this function and table_fetch_row_version is that @@ -1014,8 +1014,8 @@ extern bool table_index_fetch_tuple_check(Relation rel, * true, false otherwise. * * See table_index_fetch_tuple's comment about what the difference between - * these functions is. It is correct to use this function outside of index - * entry->table tuple lookups. + * these functions is. This function is the correct to use outside of + * index entry->table tuple lookups. */ static inline bool table_tuple_fetch_row_version(Relation rel, diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 4e2fb39105..11027cdb10 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -220,6 +220,7 @@ typedef enum NodeTag T_PlannerInfo, T_PlannerGlobal, T_RelOptInfo, + T_RelInfoList, T_IndexOptInfo, T_ForeignKeyOptInfo, T_ParamPathInfo, diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 441e64eca9..38dc186623 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -236,15 +236,9 @@ struct PlannerInfo /* * join_rel_list is a list of all join-relation RelOptInfos we have - * considered in this planning run. For small problems we just scan the - * list to do lookups, but when there are many join relations we build a - * hash table for faster lookups. The hash table is present and valid - * when join_rel_hash is not NULL. Note that we still maintain the list - * even when using the hash table for lookups; this simplifies life for - * GEQO. + * considered in this planning run. */ - List *join_rel_list; /* list of join-relation RelOptInfos */ - struct HTAB *join_rel_hash; /* optional hashtable for join relations */ + struct RelInfoList *join_rel_list; /* list of join-relation RelOptInfos */ /* * When doing a dynamic-programming-style join search, join_rel_level[k] @@ -742,6 +736,24 @@ typedef struct RelOptInfo ((rel)->part_scheme && (rel)->boundinfo && (rel)->nparts > 0 && \ (rel)->part_rels && (rel)->partexprs && (rel)->nullable_partexprs) +/* + * RelInfoList + * A list to store relation specific info and to retrieve it by relids. + * + * For small problems we just scan the list to do lookups, but when there are + * many relations we build a hash table for faster lookups. The hash table is + * present and valid when rel_hash is not NULL. Note that we still maintain + * the list even when using the hash table for lookups; this simplifies life + * for GEQO. + */ +typedef struct RelInfoList +{ + NodeTag type; + + List *items; + struct HTAB *hash; +} RelInfoList; + /* * IndexOptInfo * Per-index information for planning/optimization diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 2eecb1744b..841bd8bc67 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1086,19 +1086,6 @@ explain (costs off) select * from boolpart where a is not unknown; Filter: (a IS NOT UNKNOWN) (7 rows) -create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); -create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); -create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); -create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); -create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); --- try a more complex case that's been known to trip up pruning in the past -explain (costs off) select * from boolrangep where not a and not b and c = 25; - QUERY PLAN ----------------------------------------------- - Seq Scan on boolrangep_ff1 - Filter: ((NOT a) AND (NOT b) AND (c = 25)) -(2 rows) - -- test scalar-to-array operators create table coercepart (a varchar) partition by list (a); create table coercepart_ab partition of coercepart for values in ('ab'); @@ -1433,7 +1420,7 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000; Filter: (a > '100000000000000'::bigint) (2 rows) -drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; +drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; -- -- Test Partition pruning for HASH partitioning -- diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index c64151ba09..cd2b550c14 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -2094,30 +2094,6 @@ NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW drop table parted_trig; --- Verify propagation of trigger arguments to partitions -create table parted_trig (a int) partition by list (a); -create table parted_trig1 partition of parted_trig for values in (1); -create or replace function trigger_notice() returns trigger as $$ - declare - arg1 text = TG_ARGV[0]; - arg2 integer = TG_ARGV[1]; - begin - raise notice 'trigger % on % % % for % args % %', - TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, arg1, arg2; - return null; - end; - $$ language plpgsql; -create trigger aaa after insert on parted_trig - for each row execute procedure trigger_notice('quirky', 1); --- Verify propagation of trigger arguments to partitions attached after creating trigger -create table parted_trig2 partition of parted_trig for values in (2); -create table parted_trig3 (like parted_trig); -alter table parted_trig attach partition parted_trig3 for values in (3); -insert into parted_trig values (1), (2), (3); -NOTICE: trigger aaa on parted_trig1 AFTER INSERT for ROW args quirky 1 -NOTICE: trigger aaa on parted_trig2 AFTER INSERT for ROW args quirky 1 -NOTICE: trigger aaa on parted_trig3 AFTER INSERT for ROW args quirky 1 -drop table parted_trig; -- test irregular partitions (i.e., different column definitions), -- including that the WHEN clause works create function bark(text) returns bool language plpgsql immutable diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index 7bb4e2fffc..071e28dce8 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -159,15 +159,6 @@ explain (costs off) select * from boolpart where a is not true and a is not fals explain (costs off) select * from boolpart where a is unknown; explain (costs off) select * from boolpart where a is not unknown; -create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); -create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); -create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); -create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); -create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); - --- try a more complex case that's been known to trip up pruning in the past -explain (costs off) select * from boolrangep where not a and not b and c = 25; - -- test scalar-to-array operators create table coercepart (a varchar) partition by list (a); create table coercepart_ab partition of coercepart for values in ('ab'); @@ -273,7 +264,7 @@ create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values fr -- all partitions but rparted_by_int2_maxvalue pruned explain (costs off) select * from rparted_by_int2 where a > 100000000000000; -drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; +drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; -- -- Test Partition pruning for HASH partitioning diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 4534dc9ebe..8f833b7d10 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1460,29 +1460,6 @@ create trigger qqq after insert on parted_trig_1_1 for each row execute procedur insert into parted_trig values (50), (1500); drop table parted_trig; --- Verify propagation of trigger arguments to partitions -create table parted_trig (a int) partition by list (a); -create table parted_trig1 partition of parted_trig for values in (1); -create or replace function trigger_notice() returns trigger as $$ - declare - arg1 text = TG_ARGV[0]; - arg2 integer = TG_ARGV[1]; - begin - raise notice 'trigger % on % % % for % args % %', - TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, arg1, arg2; - return null; - end; - $$ language plpgsql; -create trigger aaa after insert on parted_trig - for each row execute procedure trigger_notice('quirky', 1); - --- Verify propagation of trigger arguments to partitions attached after creating trigger -create table parted_trig2 partition of parted_trig for values in (2); -create table parted_trig3 (like parted_trig); -alter table parted_trig attach partition parted_trig3 for values in (3); -insert into parted_trig values (1), (2), (3); -drop table parted_trig; - -- test irregular partitions (i.e., different column definitions), -- including that the WHEN clause works create function bark(text) returns bool language plpgsql immutable -- 2.16.4 --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v13-0002-Introduce-make_join_rel_common-function.patch From 1a42a4e80702fc2741bc603ab5a08835a06bc51e Mon Sep 17 00:00:00 2001 From: Antonin Houska Date: Fri, 12 Jul 2019 10:04:32 +0200 Subject: [PATCH 2/3] Introduce make_join_rel_common function. This diff tries to make the following patch a bit easier to read. --- src/backend/optimizer/path/joinrels.c | 39 ++++++++++++++++++++++------------- 1 file changed, 25 insertions(+), 14 deletions(-) diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 43c3b7ea48..a9fc52c61f 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -35,6 +35,8 @@ static bool has_legal_joinclause(PlannerInfo *root, RelOptInfo *rel); static bool restriction_is_constant_false(List *restrictlist, RelOptInfo *joinrel, bool only_pushed_down); +static RelOptInfo *make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, + RelOptInfo *rel2); static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, SpecialJoinInfo *sjinfo, List *restrictlist); @@ -647,21 +649,12 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, return true; } - /* - * make_join_rel - * Find or create a join RelOptInfo that represents the join of - * the two given rels, and add to it path information for paths - * created with the two rels as outer and inner rel. - * (The join rel may already contain paths generated from other - * pairs of rels that add up to the same set of base rels.) - * - * NB: will return NULL if attempted join is not valid. This can happen - * when working with outer joins, or with IN or EXISTS clauses that have been - * turned into joins. - */ -RelOptInfo * -make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) + * make_join_rel_common + * The workhorse of make_join_rel(). + */ +static RelOptInfo * +make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) { Relids joinrelids; SpecialJoinInfo *sjinfo; @@ -743,6 +736,24 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) return joinrel; } +/* + * make_join_rel + * Find or create a join RelOptInfo that represents the join of + * the two given rels, and add to it path information for paths + * created with the two rels as outer and inner rel. + * (The join rel may already contain paths generated from other + * pairs of rels that add up to the same set of base rels.) + * + * NB: will return NULL if attempted join is not valid. This can happen when + * working with outer joins, or with IN or EXISTS clauses that have been + * turned into joins. + */ +RelOptInfo * +make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) +{ + return make_join_rel_common(root, rel1, rel2); +} + /* * populate_joinrel_with_paths * Add paths to the given joinrel for given pair of joining relations. The -- 2.16.4 --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v13-0003-Aggregate-push-down-basic-functionality.patch From fbb8271ee1c2608552aee905f0a798f3b19f1a17 Mon Sep 17 00:00:00 2001 From: Antonin Houska Date: Fri, 12 Jul 2019 10:04:32 +0200 Subject: [PATCH 3/3] Aggregate push-down - basic functionality. --- src/backend/nodes/copyfuncs.c | 21 +- src/backend/nodes/outfuncs.c | 36 ++ src/backend/optimizer/README | 87 +++ src/backend/optimizer/path/allpaths.c | 112 ++++ src/backend/optimizer/path/costsize.c | 17 +- src/backend/optimizer/path/equivclass.c | 134 +++++ src/backend/optimizer/path/joinrels.c | 170 +++++- src/backend/optimizer/plan/initsplan.c | 257 ++++++++ src/backend/optimizer/plan/planagg.c | 2 +- src/backend/optimizer/plan/planmain.c | 23 +- src/backend/optimizer/plan/planner.c | 58 +- src/backend/optimizer/plan/setrefs.c | 33 ++ src/backend/optimizer/prep/prepjointree.c | 1 + src/backend/optimizer/util/pathnode.c | 143 ++++- src/backend/optimizer/util/relnode.c | 914 +++++++++++++++++++++++++++-- src/backend/optimizer/util/tlist.c | 56 ++ src/backend/utils/misc/guc.c | 9 + src/include/nodes/nodes.h | 2 + src/include/nodes/pathnodes.h | 99 ++++ src/include/optimizer/clauses.h | 2 + src/include/optimizer/pathnode.h | 19 +- src/include/optimizer/paths.h | 7 + src/include/optimizer/planmain.h | 4 +- src/include/optimizer/tlist.h | 8 +- src/test/regress/expected/agg_pushdown.out | 217 +++++++ src/test/regress/expected/sysviews.out | 3 +- src/test/regress/parallel_schedule | 3 + src/test/regress/serial_schedule | 1 + 28 files changed, 2365 insertions(+), 73 deletions(-) create mode 100644 src/test/regress/expected/agg_pushdown.out diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 78deade89b..56f99e2a59 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2204,8 +2204,8 @@ _copyOnConflictExpr(const OnConflictExpr *from) /* **************************************************************** * pathnodes.h copy functions * - * We don't support copying RelOptInfo, IndexOptInfo, or Path nodes. - * There are some subsidiary structs that are useful to copy, though. + * We don't support copying RelOptInfo, IndexOptInfo, RelAggInfo or Path + * nodes. There are some subsidiary structs that are useful to copy, though. * **************************************************************** */ @@ -2346,6 +2346,20 @@ _copyPlaceHolderInfo(const PlaceHolderInfo *from) return newnode; } +static GroupedVarInfo * +_copyGroupedVarInfo(const GroupedVarInfo *from) +{ + GroupedVarInfo *newnode = makeNode(GroupedVarInfo); + + COPY_NODE_FIELD(gvexpr); + COPY_NODE_FIELD(agg_partial); + COPY_SCALAR_FIELD(sortgroupref); + COPY_SCALAR_FIELD(gv_eval_at); + COPY_SCALAR_FIELD(derived); + + return newnode; +} + /* **************************************************************** * parsenodes.h copy functions * **************************************************************** @@ -5124,6 +5138,9 @@ copyObjectImpl(const void *from) case T_PlaceHolderInfo: retval = _copyPlaceHolderInfo(from); break; + case T_GroupedVarInfo: + retval = _copyGroupedVarInfo(from); + break; /* * VALUE NODES diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 4529b5c63b..518aeeefe0 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2190,6 +2190,8 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node) WRITE_BITMAPSET_FIELD(all_baserels); WRITE_BITMAPSET_FIELD(nullable_baserels); WRITE_NODE_FIELD(join_rel_list); + WRITE_NODE_FIELD(grouped_rel_list); + WRITE_NODE_FIELD(agg_info_list); WRITE_INT_FIELD(join_cur_level); WRITE_NODE_FIELD(init_plans); WRITE_NODE_FIELD(cte_plan_ids); @@ -2203,6 +2205,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node) WRITE_NODE_FIELD(append_rel_list); WRITE_NODE_FIELD(rowMarks); WRITE_NODE_FIELD(placeholder_list); + WRITE_NODE_FIELD(grouped_var_list); WRITE_NODE_FIELD(fkey_list); WRITE_NODE_FIELD(query_pathkeys); WRITE_NODE_FIELD(group_pathkeys); @@ -2210,6 +2213,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node) WRITE_NODE_FIELD(distinct_pathkeys); WRITE_NODE_FIELD(sort_pathkeys); WRITE_NODE_FIELD(processed_tlist); + WRITE_INT_FIELD(max_sortgroupref); WRITE_NODE_FIELD(minmax_aggs); WRITE_FLOAT_FIELD(total_table_pages, "%.0f"); WRITE_FLOAT_FIELD(tuple_fraction, "%.4f"); @@ -2428,6 +2432,20 @@ _outParamPathInfo(StringInfo str, const ParamPathInfo *node) WRITE_NODE_FIELD(ppi_clauses); } +static void +_outRelAggInfo(StringInfo str, const RelAggInfo *node) +{ + WRITE_NODE_TYPE("RELAGGINFO"); + + WRITE_BITMAPSET_FIELD(relids); + WRITE_NODE_FIELD(target); + WRITE_NODE_FIELD(agg_input); + WRITE_FLOAT_FIELD(input_rows, "%.0f"); + WRITE_NODE_FIELD(group_clauses); + WRITE_NODE_FIELD(group_exprs); + WRITE_NODE_FIELD(agg_exprs); +} + static void _outRestrictInfo(StringInfo str, const RestrictInfo *node) { @@ -2527,6 +2545,18 @@ _outPlaceHolderInfo(StringInfo str, const PlaceHolderInfo *node) WRITE_INT_FIELD(ph_width); } +static void +_outGroupedVarInfo(StringInfo str, const GroupedVarInfo *node) +{ + WRITE_NODE_TYPE("GROUPEDVARINFO"); + + WRITE_NODE_FIELD(gvexpr); + WRITE_NODE_FIELD(agg_partial); + WRITE_UINT_FIELD(sortgroupref); + WRITE_BITMAPSET_FIELD(gv_eval_at); + WRITE_BOOL_FIELD(derived); +} + static void _outMinMaxAggInfo(StringInfo str, const MinMaxAggInfo *node) { @@ -4084,6 +4114,9 @@ outNode(StringInfo str, const void *obj) case T_ParamPathInfo: _outParamPathInfo(str, obj); break; + case T_RelAggInfo: + _outRelAggInfo(str, obj); + break; case T_RestrictInfo: _outRestrictInfo(str, obj); break; @@ -4102,6 +4135,9 @@ outNode(StringInfo str, const void *obj) case T_PlaceHolderInfo: _outPlaceHolderInfo(str, obj); break; + case T_GroupedVarInfo: + _outGroupedVarInfo(str, obj); + break; case T_MinMaxAggInfo: _outMinMaxAggInfo(str, obj); break; diff --git a/src/backend/optimizer/README b/src/backend/optimizer/README index 89ce373d5e..4633fb8768 100644 --- a/src/backend/optimizer/README +++ b/src/backend/optimizer/README @@ -1127,3 +1127,90 @@ breaking down aggregation or grouping over a partitioned relation into aggregation or grouping over its partitions is called partitionwise aggregation. Especially when the partition keys match the GROUP BY clause, this can be significantly faster than the regular method. + +Aggregate push-down +------------------- + +The obvious way to evaluate aggregates is to evaluate the FROM clause of the +SQL query (this is what query_planner does) and use the resuing paths as the +input of Agg node. However, if the groups are large enough, it may be more +efficient to apply the partial aggregation to the output of base relation +scan, and finalize it when we have all relations of the query joined: + + EXPLAIN + SELECT a.i, avg(b.u) + FROM a JOIN b ON b.j = a.i + GROUP BY a.i; + + Finalize HashAggregate + Group Key: a.i + -> Nested Loop + -> Partial HashAggregate + Group Key: b.j + -> Seq Scan on b + -> Index Only Scan using a_pkey on a + Index Cond: (i = b.j) + +Thus the join above the partial aggregate node receives fewer input rows, and +so the number of outer-to-inner pairs of tuples to be checked can be +significantly lower, which can in turn lead to considerably lower join cost. + +Note that there's often no GROUP BY expression to be used for the partial +aggregation, so we use equivalence classes to derive grouping expression: in +the example above, the grouping key "b.j" was derived from "a.i". + +Furthermore, extra grouping columns can be added to the partial Agg node if a +join clause above that node references a column which is not in the query +GROUP BY clause and which could not be derived using equivalence class. + + EXPLAIN + SELECT a.i, avg(b.u) + FROM a JOIN b ON b.j = a.x + GROUP BY a.i; + + Finalize HashAggregate + Group Key: a.i + -> Hash Join + Hash Cond: (a.x = b.j) + -> Seq Scan on a + -> Hash + -> Partial HashAggregate + Group Key: b.j + -> Seq Scan on b + +Here the partial aggregate uses "b.j" as grouping column although it's not in +the same equivalence class as "a.i". Note that no column of the {a.x, b.j} +equivalence class is used as a key for the final aggregation. + +Besides base relation, the aggregation can also be pushed down to join: + + EXPLAIN + SELECT a.i, avg(b.u + c.v) + FROM a JOIN b ON b.j = a.i + JOIN c ON c.k = a.i + WHERE b.j = c.k GROUP BY a.i; + + Finalize HashAggregate + Group Key: a.i + -> Hash Join + Hash Cond: (b.j = a.i) + -> Partial HashAggregate + Group Key: b.j + -> Hash Join + Hash Cond: (b.j = c.k) + -> Seq Scan on b + -> Hash + -> Seq Scan on c + -> Hash + -> Seq Scan on a + +Whether the Agg node is created out of base relation or out of join, it's +added to a separate RelOptInfo that we call "grouped relation". Grouped +relation can be joined to a non-grouped relation, which results in a grouped +relation too. Join of two grouped relations does not seem to be very useful +and is currently not supported. + +If query_planner produces a grouped relation that contains valid paths, these +are simply added to the UPPERREL_PARTIAL_GROUP_AGG relation. Further +processing of these paths then does not differ from processing of other +partially grouped paths. diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index b7723481b0..f14eb357dc 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -60,6 +60,7 @@ typedef struct pushdown_safety_info /* These parameters are set by GUC */ bool enable_geqo = false; /* just in case GUC doesn't set it */ +bool enable_agg_pushdown; int geqo_threshold; int min_parallel_table_scan_size; int min_parallel_index_scan_size; @@ -124,6 +125,9 @@ static void set_result_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte); static void set_worktable_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte); +static void add_grouped_path(PlannerInfo *root, RelOptInfo *rel, + Path *subpath, AggStrategy aggstrategy, + RelAggInfo *agg_info); static RelOptInfo *make_rel_from_joinlist(PlannerInfo *root, List *joinlist); static bool subquery_is_pushdown_safe(Query *subquery, Query *topquery, pushdown_safety_info *safetyInfo); @@ -2730,6 +2734,80 @@ generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows) } } +/* + * generate_grouping_paths + * Create partially aggregated paths and add them to grouped relation. + * + * "rel_plain" is base or join relation whose paths are not grouped. + */ +void +generate_grouping_paths(PlannerInfo *root, RelOptInfo *rel_grouped, + RelOptInfo *rel_plain, RelAggInfo *agg_info) +{ + ListCell *lc; + + if (IS_DUMMY_REL(rel_plain)) + { + mark_dummy_rel(rel_grouped); + return; + } + + foreach(lc, rel_plain->pathlist) + { + Path *path = (Path *) lfirst(lc); + + /* + * Since the path originates from the non-grouped relation which is + * not aware of the aggregate push-down, we must ensure that it + * provides the correct input for aggregation. + */ + path = (Path *) create_projection_path(root, rel_grouped, path, + agg_info->agg_input); + + /* + * add_grouped_path() will check whether the path has suitable + * pathkeys. + */ + add_grouped_path(root, rel_grouped, path, AGG_SORTED, agg_info); + + /* + * Repeated creation of hash table (for new parameter values) should + * be possible, does not sound like a good idea in terms of + * efficiency. + */ + if (path->param_info == NULL) + add_grouped_path(root, rel_grouped, path, AGG_HASHED, agg_info); + } + + /* Could not generate any grouped paths? */ + if (rel_grouped->pathlist == NIL) + mark_dummy_rel(rel_grouped); +} + +/* + * Apply partial aggregation to a subpath and add the AggPath to the pathlist. + */ +static void +add_grouped_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, + AggStrategy aggstrategy, RelAggInfo *agg_info) +{ + Path *agg_path; + + + if (aggstrategy == AGG_HASHED) + agg_path = (Path *) create_agg_hashed_path(root, rel, subpath, + agg_info); + else if (aggstrategy == AGG_SORTED) + agg_path = (Path *) create_agg_sorted_path(root, rel, subpath, + agg_info); + else + elog(ERROR, "unexpected strategy %d", aggstrategy); + + /* Add the grouped path to the list of grouped base paths. */ + if (agg_path != NULL) + add_path(rel, (Path *) agg_path); +} + /* * make_rel_from_joinlist * Build access paths using a "joinlist" to guide the join path search. @@ -2770,6 +2848,34 @@ make_rel_from_joinlist(PlannerInfo *root, List *joinlist) int varno = ((RangeTblRef *) jlnode)->rtindex; thisrel = find_base_rel(root, varno); + + /* + * Create a grouped relation to facilitate aggregate push-down. + * This makes no sense if thisrel is the only relation of the + * query. + */ + if (bms_nonempty_difference(root->all_baserels, thisrel->relids)) + { + RelOptInfo *rel_grouped; + RelAggInfo *agg_info; + + /* + * Build grouped relation if thisrel is suitable for partial + * aggregation. + */ + rel_grouped = build_simple_grouped_rel(root, varno, &agg_info); + + if (rel_grouped) + { + /* Make the relation available for joining. */ + add_grouped_rel(root, rel_grouped, agg_info); + + /* Add the aggregation paths to it. */ + generate_grouping_paths(root, rel_grouped, thisrel, + agg_info); + set_cheapest(rel_grouped); + } + } } else if (IsA(jlnode, List)) { @@ -2871,6 +2977,7 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) for (lev = 2; lev <= levels_needed; lev++) { + RelOptInfo *rel_grouped; ListCell *lc; /* @@ -2907,6 +3014,11 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) /* Find and save the cheapest paths for this rel */ set_cheapest(rel); + /* The same for grouped relation if one exists. */ + rel_grouped = find_grouped_rel(root, rel->relids, NULL); + if (rel_grouped) + set_cheapest(rel_grouped); + #ifdef OPTIMIZER_DEBUG debug_print_rel(root, rel); #endif diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index a2a9b1f7be..f4d90b8fcf 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -4414,7 +4414,6 @@ set_baserel_size_estimates(PlannerInfo *root, RelOptInfo *rel) 0, JOIN_INNER, NULL); - rel->rows = clamp_row_est(nrows); cost_qual_eval(&rel->baserestrictcost, rel->baserestrictinfo, root); @@ -5392,11 +5391,11 @@ set_pathtarget_cost_width(PlannerInfo *root, PathTarget *target) foreach(lc, target->exprs) { Node *node = (Node *) lfirst(lc); + int32 item_width; if (IsA(node, Var)) { Var *var = (Var *) node; - int32 item_width; /* We should not see any upper-level Vars here */ Assert(var->varlevelsup == 0); @@ -5427,6 +5426,20 @@ set_pathtarget_cost_width(PlannerInfo *root, PathTarget *target) Assert(item_width > 0); tuple_width += item_width; } + else if (IsA(node, Aggref)) + { + /* + * If the target is evaluated by AggPath, it'll care of cost + * estimate. If the target is above AggPath (typically target of a + * join relation that contains grouped relation), the cost of + * Aggref should not be accounted for again. + * + * On the other hand, width is always needed. + */ + item_width = get_typavgwidth(exprType(node), exprTypmod(node)); + Assert(item_width > 0); + tuple_width += item_width; + } else { /* diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 688d9b0707..33f2aad19c 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -2542,6 +2542,140 @@ is_redundant_derived_clause(RestrictInfo *rinfo, List *clauselist) return false; } +/* + * translate_expression_to_rels + * If the appropriate equivalence classes exist, replace vars in + * gvi->gvexpr with vars whose varno is equal to relid. Return NULL if + * translation is not possible or needed. + * + * Note: Currently we only translate Var expressions. This is subject to + * change as the aggregate push-down feature gets enhanced. + */ +GroupedVarInfo * +translate_expression_to_rels(PlannerInfo *root, GroupedVarInfo *gvi, + Index relid) +{ + Var *var; + ListCell *l1; + bool found_orig = false; + Var *var_translated = NULL; + GroupedVarInfo *result; + + /* Can't do anything w/o equivalence classes. */ + if (root->eq_classes == NIL) + return NULL; + + var = castNode(Var, gvi->gvexpr); + + /* + * Do we need to translate the var? + */ + if (var->varno == relid) + return NULL; + + /* + * Find the replacement var. + */ + foreach(l1, root->eq_classes) + { + EquivalenceClass *ec = lfirst_node(EquivalenceClass, l1); + ListCell *l2; + + /* TODO Check if any other EC kind should be ignored. */ + if (ec->ec_has_volatile || ec->ec_below_outer_join || ec->ec_broken) + continue; + + /* Single-element EC can hardly help in translations. */ + if (list_length(ec->ec_members) == 1) + continue; + + /* + * Collect all vars of this EC and their varnos. + * + * ec->ec_relids does not help because we're only interested in a + * subset of EC members. + */ + foreach(l2, ec->ec_members) + { + EquivalenceMember *em = lfirst_node(EquivalenceMember, l2); + Var *ec_var; + + /* + * The grouping expressions derived here are used to evaluate + * possibility to push aggregation down to RELOPT_BASEREL or + * RELOPT_JOINREL relations, and to construct reltargets for the + * grouped rels. We're not interested at the moment whether the + * relations do have children. + */ + if (em->em_is_child) + continue; + + if (!IsA(em->em_expr, Var)) + continue; + + ec_var = castNode(Var, em->em_expr); + if (equal(ec_var, var)) + found_orig = true; + else if (ec_var->varno == relid) + var_translated = ec_var; + + if (found_orig && var_translated) + { + /* + * The replacement Var must have the same data type, otherwise + * the values are not guaranteed to be grouped in the same way + * as values of the original Var. + */ + if (ec_var->vartype != var->vartype) + return NULL; + + break; + } + } + + if (found_orig) + { + /* + * The same expression probably does not exist in multiple ECs. + */ + if (var_translated == NULL) + { + /* + * Failed to translate the expression. + */ + return NULL; + } + else + { + /* Success. */ + break; + } + } + else + { + /* + * Vars of the requested relid can be in the next ECs too. + */ + var_translated = NULL; + } + } + + if (!found_orig) + return NULL; + + result = makeNode(GroupedVarInfo); + memcpy(result, gvi, sizeof(GroupedVarInfo)); + + /* + * translate_expression_to_rels_mutator updates gv_eval_at. + */ + result->gv_eval_at = bms_make_singleton(relid); + result->gvexpr = (Expr *) var_translated; + result->derived = true; + + return result; +} + /* * is_redundant_with_indexclauses * Test whether rinfo is redundant with any clause in the IndexClause diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index a9fc52c61f..5eea155f0c 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -22,6 +22,7 @@ #include "partitioning/partbounds.h" #include "utils/lsyscache.h" #include "utils/memutils.h" +#include "utils/selfuncs.h" static void make_rels_by_clause_joins(PlannerInfo *root, @@ -36,7 +37,9 @@ static bool restriction_is_constant_false(List *restrictlist, RelOptInfo *joinrel, bool only_pushed_down); static RelOptInfo *make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, - RelOptInfo *rel2); + RelOptInfo *rel2, + RelAggInfo *agg_info, + RelOptInfo *rel_agg_input); static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, RelOptInfo *joinrel, SpecialJoinInfo *sjinfo, List *restrictlist); @@ -652,9 +655,17 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, /* * make_join_rel_common * The workhorse of make_join_rel(). - */ + * + * 'agg_info' contains the reltarget of grouped relation and everything we + * need to aggregate the join result. If NULL, then the join relation should + * not be grouped. + * + * 'rel_agg_input' describes the AggPath input relation if the join output + * should be aggregated. If NULL is passed, do not aggregate the join output. + */ static RelOptInfo * -make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) +make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2, + RelAggInfo *agg_info, RelOptInfo *rel_agg_input) { Relids joinrelids; SpecialJoinInfo *sjinfo; @@ -715,7 +726,7 @@ make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) * goes with this particular joining. */ joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo, - &restrictlist); + &restrictlist, agg_info); /* * If we've already proven this join is empty, we needn't consider any @@ -728,14 +739,88 @@ make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) } /* Add paths to the join relation. */ - populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo, - restrictlist); + if (rel_agg_input == NULL) + { + /* + * Simply join the input relations, whether both are plain or one of + * them is grouped. + */ + populate_joinrel_with_paths(root, rel1, rel2, joinrel, sjinfo, + restrictlist); + } + else + { + /* The join relation is grouped. */ + Assert(agg_info != NULL); + + /* + * Apply partial aggregation to the paths of rel_agg_input and add the + * resulting paths to joinrel. + */ + generate_grouping_paths(root, joinrel, rel_agg_input, agg_info); + } bms_free(joinrelids); return joinrel; } +/* + * make_join_rel_combined + * Join grouped relation to non-grouped one. + */ +static void +make_join_rel_combined(PlannerInfo *root, RelOptInfo *rel1, + RelOptInfo *rel2, + RelAggInfo *agg_info) +{ + RelOptInfo *rel1_grouped; + RelOptInfo *rel2_grouped; + bool rel1_grouped_useful = false; + bool rel2_grouped_useful = false; + + /* Retrieve the grouped relations. */ + rel1_grouped = find_grouped_rel(root, rel1->relids, NULL); + rel2_grouped = find_grouped_rel(root, rel2->relids, NULL); + + /* + * Dummy rel may indicate a join relation that is able to generate grouped + * paths as such (i.e. it has valid agg_info), but for which the path + * actually could not be created (e.g. only AGG_HASHED strategy was + * possible but work_mem was not sufficient for hash table). + */ + rel1_grouped_useful = rel1_grouped != NULL && !IS_DUMMY_REL(rel1_grouped); + rel2_grouped_useful = rel2_grouped != NULL && !IS_DUMMY_REL(rel2_grouped); + + /* Nothing to do if there's no grouped relation. */ + if (!rel1_grouped_useful && !rel2_grouped_useful) + return; + + /* + * At maximum one input rel can be grouped (here we don't care if any rel + * is eventually dummy, the existence of grouped rel indicates that + * aggregates can be pushed down to it). If both were grouped, then + * grouping of one side would change the occurrence of the other side's + * aggregate transient states on the input of the final aggregation. This + * can be handled by adjusting the transient states, but it's not worth + * the effort because it's hard to find a use case for this kind of join. + * + * XXX If the join of two grouped rels is implemented someday, note that + * both rels can have aggregates, so it'd be hard to join grouped rel to + * non-grouped here: 1) such a "mixed join" would require a special + * target, 2) both AGGSPLIT_FINAL_DESERIAL and AGGSPLIT_SIMPLE aggregates + * could appear in the target of the final aggregation node, originating + * from the grouped and the non-grouped input rel respectively. + */ + if (rel1_grouped && rel2_grouped) + return; + + if (rel1_grouped_useful) + make_join_rel_common(root, rel1_grouped, rel2, agg_info, NULL); + else if (rel2_grouped_useful) + make_join_rel_common(root, rel1, rel2_grouped, agg_info, NULL); +} + /* * make_join_rel * Find or create a join RelOptInfo that represents the join of @@ -744,14 +829,83 @@ make_join_rel_common(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) * (The join rel may already contain paths generated from other * pairs of rels that add up to the same set of base rels.) * + * In addition to creating an ordinary join relation, try to create a + * grouped one. There are two strategies to achieve that: join a grouped + * relation to plain one, or join two plain relations and apply partial + * aggregation to the result. + * * NB: will return NULL if attempted join is not valid. This can happen when * working with outer joins, or with IN or EXISTS clauses that have been - * turned into joins. + * turned into joins. Besides that, NULL is also returned if caller is + * interested in a grouped relation but it could not be created. + * + * Only the plain relation is returned; if grouped relation exists, it can be + * retrieved using find_grouped_rel(). */ RelOptInfo * make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) { - return make_join_rel_common(root, rel1, rel2); + Relids joinrelids; + RelAggInfo *agg_info = NULL; + RelOptInfo *joinrel, + *joinrel_plain; + + /* 1) form the plain join. */ + joinrel = make_join_rel_common(root, rel1, rel2, NULL, NULL); + joinrel_plain = joinrel; + + if (joinrel_plain == NULL) + return joinrel_plain; + + /* + * We're done if there are no grouping expressions nor aggregates. + */ + if (root->grouped_var_list == NIL) + return joinrel_plain; + + joinrelids = bms_union(rel1->relids, rel2->relids); + joinrel = find_grouped_rel(root, joinrelids, &agg_info); + + if (joinrel != NULL) + { + /* + * If the same grouped joinrel was already formed, just with the base + * rels divided between rel1 and rel2 in a different way, the matching + * agg_info should already be there. + */ + Assert(agg_info != NULL); + } + else + { + /* + * agg_info must be created from scratch. + */ + agg_info = create_rel_agg_info(root, joinrel_plain); + + /* Cannot we build grouped join? */ + if (agg_info == NULL) + return joinrel_plain; + + /* + * The number of aggregate input rows is simply the number of rows of + * the non-grouped relation, which should have been estimated by now. + */ + agg_info->input_rows = joinrel_plain->rows; + } + + /* + * 2) join two plain rels and aggregate the join paths. Aggregate + * push-down only makes sense if the join is not the top-level one. + */ + if (bms_nonempty_difference(root->all_baserels, joinrelids)) + make_join_rel_common(root, rel1, rel2, agg_info, joinrel_plain); + + /* + * 3) combine plain and grouped relations. + */ + make_join_rel_combined(root, rel1, rel2, agg_info); + + return joinrel_plain; } /* diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 73da0c2d8e..9f97a2dd8f 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -48,6 +48,8 @@ typedef struct PostponedQual } PostponedQual; +static void create_aggregate_grouped_var_infos(PlannerInfo *root); +static void create_grouping_expr_grouped_var_infos(PlannerInfo *root); static void extract_lateral_references(PlannerInfo *root, RelOptInfo *brel, Index rtindex); static List *deconstruct_recurse(PlannerInfo *root, Node *jtnode, @@ -273,6 +275,261 @@ add_vars_to_targetlist(PlannerInfo *root, List *vars, } } +/* + * Add GroupedVarInfo to grouped_var_list for each aggregate as well as for + * each possible grouping expression. + * + * root->group_pathkeys must be setup before this function is called. + */ +extern void +setup_aggregate_pushdown(PlannerInfo *root) +{ + ListCell *lc; + + /* + * Isn't user interested in the aggregate push-down feature? + */ + if (!enable_agg_pushdown) + return; + + /* The feature can only be applied to grouped aggregation. */ + if (!root->parse->groupClause) + return; + + /* + * Grouping sets require multiple different groupings but the base + * relation can only generate one. + */ + if (root->parse->groupingSets) + return; + + /* + * SRF is not allowed in the aggregate argument and we don't even want it + * in the GROUP BY clause, so forbid it in general. It needs to be + * analyzed if evaluation of a GROUP BY clause containing SRF below the + * query targetlist would be correct. Currently it does not seem to be an + * important use case. + */ + if (root->parse->hasTargetSRFs) + return; + + /* Create GroupedVarInfo per (distinct) aggregate. */ + create_aggregate_grouped_var_infos(root); + + /* Isn't there any aggregate to be pushed down? */ + if (root->grouped_var_list == NIL) + return; + + /* Create GroupedVarInfo per grouping expression. */ + create_grouping_expr_grouped_var_infos(root); + + /* Isn't there any useful grouping expression for aggregate push-down? */ + if (root->grouped_var_list == NIL) + return; + + /* + * Now that we know that grouping can be pushed down, search for the + * maximum sortgroupref. The base relations may need it if extra grouping + * expressions get added to them. + */ + Assert(root->max_sortgroupref == 0); + foreach(lc, root->processed_tlist) + { + TargetEntry *te = lfirst_node(TargetEntry, lc); + + if (te->ressortgroupref > root->max_sortgroupref) + root->max_sortgroupref = te->ressortgroupref; + } +} + +/* + * Create GroupedVarInfo for each distinct aggregate. + * + * If any aggregate is not suitable, set root->grouped_var_list to NIL and + * return. + */ +static void +create_aggregate_grouped_var_infos(PlannerInfo *root) +{ + List *tlist_exprs; + ListCell *lc; + + Assert(root->grouped_var_list == NIL); + + tlist_exprs = pull_var_clause((Node *) root->processed_tlist, + PVC_INCLUDE_AGGREGATES | + PVC_INCLUDE_WINDOWFUNCS); + + /* + * Although GroupingFunc is related to root->parse->groupingSets, this + * field does not necessarily reflect its presence. + */ + foreach(lc, tlist_exprs) + { + Expr *expr = (Expr *) lfirst(lc); + + if (IsA(expr, GroupingFunc)) + return; + } + + /* + * Aggregates within the HAVING clause need to be processed in the same + * way as those in the main targetlist. + * + * Note that the contained aggregates will be pushed down, but the + * containing HAVING clause must be ignored until the aggregation is + * finalized. + */ + if (root->parse->havingQual != NULL) + { + List *having_exprs; + + having_exprs = pull_var_clause((Node *) root->parse->havingQual, + PVC_INCLUDE_AGGREGATES); + if (having_exprs != NIL) + tlist_exprs = list_concat(tlist_exprs, having_exprs); + } + + if (tlist_exprs == NIL) + return; + + foreach(lc, tlist_exprs) + { + Expr *expr = (Expr *) lfirst(lc); + Aggref *aggref; + ListCell *lc2; + GroupedVarInfo *gvi; + bool exists; + + /* + * tlist_exprs may also contain Vars or WindowFuncs, but we only need + * Aggrefs. + */ + if (IsA(expr, Var) ||IsA(expr, WindowFunc)) + continue; + + aggref = castNode(Aggref, expr); + + /* TODO Think if (some of) these can be handled. */ + if (aggref->aggvariadic || + aggref->aggdirectargs || aggref->aggorder || + aggref->aggdistinct) + { + /* + * Aggregation push-down is not useful if at least one aggregate + * cannot be evaluated below the top-level join. + * + * XXX Is it worth freeing the GroupedVarInfos and their subtrees? + */ + root->grouped_var_list = NIL; + break; + } + + /* Does GroupedVarInfo for this aggregate already exist? */ + exists = false; + foreach(lc2, root->grouped_var_list) + { + gvi = lfirst_node(GroupedVarInfo, lc2); + + if (equal(expr, gvi->gvexpr)) + { + exists = true; + break; + } + } + + /* Construct a new GroupedVarInfo if does not exist yet. */ + if (!exists) + { + Relids relids; + + gvi = makeNode(GroupedVarInfo); + gvi->gvexpr = (Expr *) copyObject(aggref); + + /* Find out where the aggregate should be evaluated. */ + relids = pull_varnos((Node *) aggref); + if (!bms_is_empty(relids)) + gvi->gv_eval_at = relids; + else + gvi->gv_eval_at = NULL; + + root->grouped_var_list = lappend(root->grouped_var_list, gvi); + } + } + + list_free(tlist_exprs); +} + +/* + * Create GroupedVarInfo for each expression usable as grouping key. + * + * In addition to the expressions of the query targetlist, group_pathkeys is + * also considered the source of grouping expressions. That increases the + * chance to get the relation output grouped. + */ +static void +create_grouping_expr_grouped_var_infos(PlannerInfo *root) +{ + ListCell *l1, + *l2; + List *exprs = NIL; + List *sortgrouprefs = NIL; + + /* + * Make sure GroupedVarInfo exists for each expression usable as grouping + * key. + */ + foreach(l1, root->parse->groupClause) + { + SortGroupClause *sgClause; + TargetEntry *te; + Index sortgroupref; + + sgClause = lfirst_node(SortGroupClause, l1); + te = get_sortgroupclause_tle(sgClause, root->processed_tlist); + sortgroupref = te->ressortgroupref; + + Assert(sortgroupref > 0); + + /* + * Non-zero sortgroupref does not necessarily imply grouping + * expression: data can also be sorted by aggregate. + */ + if (IsA(te->expr, Aggref)) + continue; + + /* + * The aggregate push-down feature currently supports only plain Vars + * as grouping expressions. + */ + if (!IsA(te->expr, Var)) + { + root->grouped_var_list = NIL; + return; + } + + exprs = lappend(exprs, te->expr); + sortgrouprefs = lappend_int(sortgrouprefs, sortgroupref); + } + + /* + * Construct GroupedVarInfo for each expression. + */ + forboth(l1, exprs, l2, sortgrouprefs) + { + Var *var = lfirst_node(Var, l1); + int sortgroupref = lfirst_int(l2); + GroupedVarInfo *gvi = makeNode(GroupedVarInfo); + + gvi->gvexpr = (Expr *) copyObject(var); + gvi->sortgroupref = sortgroupref; + + /* Find out where the expression should be evaluated. */ + gvi->gv_eval_at = bms_make_singleton(var->varno); + + root->grouped_var_list = lappend(root->grouped_var_list, gvi); + } +} /***************************************************************************** * diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index 9381939c82..c4d07dfac4 100644 --- a/src/backend/optimizer/plan/planagg.c +++ b/src/backend/optimizer/plan/planagg.c @@ -442,7 +442,7 @@ build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo, subroot->tuple_fraction = 1.0; subroot->limit_tuples = 1.0; - final_rel = query_planner(subroot, minmax_qp_callback, NULL); + final_rel = query_planner(subroot, minmax_qp_callback, NULL, NULL); /* * Since we didn't go through subquery_planner() to handle the subquery, diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.c index 0b9999c8a6..4103a393e6 100644 --- a/src/backend/optimizer/plan/planmain.c +++ b/src/backend/optimizer/plan/planmain.c @@ -45,6 +45,9 @@ * qp_callback is a function to compute query_pathkeys once it's safe to do so * qp_extra is optional extra data to pass to qp_callback * + * If final_rel_grouped_p is valid, relation containing grouped paths may be + * saved to *final_rel_grouped_p. + * * Note: the PlannerInfo node also includes a query_pathkeys field, which * tells query_planner the sort order that is desired in the final output * plan. This value is *not* available at call time, but is computed by @@ -53,7 +56,8 @@ */ RelOptInfo * query_planner(PlannerInfo *root, - query_pathkeys_callback qp_callback, void *qp_extra) + query_pathkeys_callback qp_callback, void *qp_extra, + RelOptInfo **final_rel_grouped_p) { Query *parse = root->parse; List *joinlist; @@ -66,6 +70,8 @@ query_planner(PlannerInfo *root, * here. */ root->join_rel_list = makeNode(RelInfoList); + root->grouped_rel_list = makeNode(RelInfoList); + root->agg_info_list = makeNode(RelInfoList); root->join_rel_level = NULL; root->join_cur_level = 0; root->canon_pathkeys = NIL; @@ -74,6 +80,7 @@ query_planner(PlannerInfo *root, root->full_join_clauses = NIL; root->join_info_list = NIL; root->placeholder_list = NIL; + root->grouped_var_list = NIL; root->fkey_list = NIL; root->initial_rels = NIL; @@ -254,6 +261,16 @@ query_planner(PlannerInfo *root, */ extract_restriction_or_clauses(root); + /* + * If the query result can be grouped, check if any grouping can be + * performed below the top-level join. If so, setup + * root->grouped_var_list. + * + * The base relations should be fully initialized now, so that we have + * enough info to decide whether grouping is possible. + */ + setup_aggregate_pushdown(root); + /* * Now expand appendrels by adding "otherrels" for their children. We * delay this to the end so that we have as much information as possible @@ -274,5 +291,9 @@ query_planner(PlannerInfo *root, final_rel->cheapest_total_path->param_info != NULL) elog(ERROR, "failed to construct the join relation"); + if (final_rel_grouped_p) + *final_rel_grouped_p = find_grouped_rel(root, final_rel->relids, + NULL); + return final_rel; } diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 401299e542..b99b979d95 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -151,6 +151,7 @@ static double get_number_of_groups(PlannerInfo *root, List *target_list); static RelOptInfo *create_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, + RelOptInfo *input_rel_grouped, PathTarget *target, bool target_parallel_safe, const AggClauseCosts *agg_costs, @@ -164,6 +165,7 @@ static RelOptInfo *make_grouping_rel(PlannerInfo *root, RelOptInfo *input_rel, Node *havingQual); static void create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, + RelOptInfo *input_rel_grouped, RelOptInfo *grouped_rel, const AggClauseCosts *agg_costs, grouping_sets_data *gd, @@ -623,6 +625,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, memset(root->upper_rels, 0, sizeof(root->upper_rels)); memset(root->upper_targets, 0, sizeof(root->upper_targets)); root->processed_tlist = NIL; + root->max_sortgroupref = 0; root->grouping_map = NULL; root->minmax_aggs = NIL; root->qual_security_level = 0; @@ -1943,6 +1946,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, List *activeWindows = NIL; grouping_sets_data *gset_data = NULL; standard_qp_extra qp_extra; + RelOptInfo *current_rel_grouped = NULL; /* A recursive query should always have setOperations */ Assert(!root->hasRecursion); @@ -2045,7 +2049,9 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, * We also generate (in standard_qp_callback) pathkey representations * of the query's sort clause, distinct clause, etc. */ - current_rel = query_planner(root, standard_qp_callback, &qp_extra); + current_rel = query_planner(root, + standard_qp_callback, &qp_extra, + ¤t_rel_grouped); /* * Convert the query's result tlist into PathTarget format. @@ -2190,6 +2196,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, { current_rel = create_grouping_paths(root, current_rel, + current_rel_grouped, grouping_target, grouping_target_parallel_safe, &agg_costs, @@ -3798,6 +3805,7 @@ get_number_of_groups(PlannerInfo *root, static RelOptInfo * create_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, + RelOptInfo *input_rel_grouped, PathTarget *target, bool target_parallel_safe, const AggClauseCosts *agg_costs, @@ -3888,7 +3896,8 @@ create_grouping_paths(PlannerInfo *root, else extra.patype = PARTITIONWISE_AGGREGATE_NONE; - create_ordinary_grouping_paths(root, input_rel, grouped_rel, + create_ordinary_grouping_paths(root, input_rel, input_rel_grouped, + grouped_rel, agg_costs, gd, &extra, &partially_grouped_rel); } @@ -4046,6 +4055,7 @@ create_degenerate_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, */ static void create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, + RelOptInfo *input_rel_grouped, RelOptInfo *grouped_rel, const AggClauseCosts *agg_costs, grouping_sets_data *gd, @@ -4093,13 +4103,23 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, if ((extra->flags & GROUPING_CAN_PARTIAL_AGG) != 0) { bool force_rel_creation; + bool have_agg_pushdown_paths; /* - * If we're doing partitionwise aggregation at this level, force - * creation of a partially_grouped_rel so we can add partitionwise - * paths to it. + * Check if the aggregate push-down feature succeeded to generate any + * paths. Dummy relation can appear here because grouped paths are not + * guaranteed to exist for a relation. */ - force_rel_creation = (patype == PARTITIONWISE_AGGREGATE_PARTIAL); + have_agg_pushdown_paths = input_rel_grouped != NULL && + !IS_DUMMY_REL(input_rel_grouped); + + /* + * If we're doing partitionwise aggregation at this level or if + * aggregate push-down succeeded to create some paths, force creation + * of a partially_grouped_rel so we can add the related paths to it. + */ + force_rel_creation = patype == PARTITIONWISE_AGGREGATE_PARTIAL || + have_agg_pushdown_paths; partially_grouped_rel = create_partial_grouping_paths(root, @@ -4108,6 +4128,23 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, gd, extra, force_rel_creation); + + /* + * No further processing is needed for paths provided by the aggregate + * push-down feature. Simply add them to the partially grouped + * relation. + */ + if (have_agg_pushdown_paths) + { + ListCell *lc; + + foreach(lc, input_rel_grouped->pathlist) + { + Path *path = (Path *) lfirst(lc); + + add_path(partially_grouped_rel, path); + } + } } /* Set out parameter. */ @@ -4132,10 +4169,14 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, /* Gather any partially grouped partial paths. */ if (partially_grouped_rel && partially_grouped_rel->partial_pathlist) - { gather_grouping_paths(root, partially_grouped_rel); + + /* + * The non-partial paths can come either from the Gather above or from + * aggregate push-down. + */ + if (partially_grouped_rel && partially_grouped_rel->pathlist) set_cheapest(partially_grouped_rel); - } /* * Estimate number of groups. @@ -7296,6 +7337,7 @@ create_partitionwise_grouping_paths(PlannerInfo *root, /* Create grouping paths for this child relation. */ create_ordinary_grouping_paths(root, child_input_rel, + NULL, child_grouped_rel, agg_costs, gd, &child_extra, &child_partially_grouped_rel); diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index dc11f098e0..3810345b3d 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -2380,6 +2380,39 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) /* No referent found for Var */ elog(ERROR, "variable not found in subplan target lists"); } + if (IsA(node, Aggref)) + { + Aggref *aggref = castNode(Aggref, node); + + /* + * The upper plan targetlist can contain Aggref whose value has + * already been evaluated by the subplan. However this can only happen + * with specific value of aggsplit. + */ + if (aggref->aggsplit == AGGSPLIT_INITIAL_SERIAL) + { + /* See if the Aggref has bubbled up from a lower plan node */ + if (context->outer_itlist && context->outer_itlist->has_non_vars) + { + newvar = search_indexed_tlist_for_non_var((Expr *) node, + context->outer_itlist, + OUTER_VAR); + if (newvar) + return (Node *) newvar; + } + if (context->inner_itlist && context->inner_itlist->has_non_vars) + { + newvar = search_indexed_tlist_for_non_var((Expr *) node, + context->inner_itlist, + INNER_VAR); + if (newvar) + return (Node *) newvar; + } + } + + /* No referent found for Aggref */ + elog(ERROR, "Aggref not found in subplan target lists"); + } if (IsA(node, PlaceHolderVar)) { PlaceHolderVar *phv = (PlaceHolderVar *) node; diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 67eeba938d..6cb33445e2 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -891,6 +891,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, memset(subroot->upper_rels, 0, sizeof(subroot->upper_rels)); memset(subroot->upper_targets, 0, sizeof(subroot->upper_targets)); subroot->processed_tlist = NIL; + root->max_sortgroupref = 0; subroot->grouping_map = NULL; subroot->minmax_aggs = NIL; subroot->qual_security_level = 0; diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index d884d2bb00..8019ad7bd2 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -2552,8 +2552,7 @@ create_projection_path(PlannerInfo *root, pathnode->path.pathtype = T_Result; pathnode->path.parent = rel; pathnode->path.pathtarget = target; - /* For now, assume we are above any joins, so no parameterization */ - pathnode->path.param_info = NULL; + pathnode->path.param_info = subpath->param_info; pathnode->path.parallel_aware = false; pathnode->path.parallel_safe = rel->consider_parallel && subpath->parallel_safe && @@ -2992,6 +2991,146 @@ create_agg_path(PlannerInfo *root, return pathnode; } +/* + * Apply AGG_SORTED aggregation path to subpath if it's suitably sorted. + * + * NULL is returned if sorting of subpath output is not suitable. + */ +AggPath * +create_agg_sorted_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, + RelAggInfo *agg_info) +{ + Node *agg_exprs; + AggSplit aggsplit; + AggClauseCosts agg_costs; + PathTarget *target; + double dNumGroups; + ListCell *lc1; + List *key_subset = NIL; + AggPath *result = NULL; + + aggsplit = AGGSPLIT_INITIAL_SERIAL; + agg_exprs = (Node *) agg_info->agg_exprs; + target = agg_info->target; + + if (subpath->pathkeys == NIL) + return NULL; + + if (!grouping_is_sortable(root->parse->groupClause)) + return NULL; + + /* + * Find all query pathkeys that our relation does affect. + */ + foreach(lc1, root->group_pathkeys) + { + PathKey *gkey = castNode(PathKey, lfirst(lc1)); + ListCell *lc2; + + foreach(lc2, subpath->pathkeys) + { + PathKey *skey = castNode(PathKey, lfirst(lc2)); + + if (skey == gkey) + { + key_subset = lappend(key_subset, gkey); + break; + } + } + } + + if (key_subset == NIL) + return NULL; + + /* Check if AGG_SORTED is useful for the whole query. */ + if (!pathkeys_contained_in(key_subset, subpath->pathkeys)) + return NULL; + + MemSet(&agg_costs, 0, sizeof(AggClauseCosts)); + get_agg_clause_costs(root, (Node *) agg_exprs, aggsplit, &agg_costs); + + Assert(agg_info->group_exprs != NIL); + dNumGroups = estimate_num_groups(root, agg_info->group_exprs, + subpath->rows, NULL); + + /* + * qual is NIL because the HAVING clause cannot be evaluated until the + * final value of the aggregate is known. + */ + result = create_agg_path(root, rel, subpath, target, + AGG_SORTED, aggsplit, + agg_info->group_clauses, + NIL, + &agg_costs, + dNumGroups); + + /* The agg path should require no fewer parameters than the plain one. */ + result->path.param_info = subpath->param_info; + + return result; +} + +/* + * Apply AGG_HASHED aggregation to subpath. + */ +AggPath * +create_agg_hashed_path(PlannerInfo *root, RelOptInfo *rel, + Path *subpath, RelAggInfo *agg_info) +{ + bool can_hash; + Node *agg_exprs; + AggSplit aggsplit; + AggClauseCosts agg_costs; + PathTarget *target; + double dNumGroups; + double hashaggtablesize; + Query *parse = root->parse; + AggPath *result = NULL; + + /* Do not try to create hash table for each parameter value. */ + Assert(subpath->param_info == NULL); + + aggsplit = AGGSPLIT_INITIAL_SERIAL; + agg_exprs = (Node *) agg_info->agg_exprs; + target = agg_info->target; + + MemSet(&agg_costs, 0, sizeof(AggClauseCosts)); + get_agg_clause_costs(root, agg_exprs, aggsplit, &agg_costs); + + can_hash = (parse->groupClause != NIL && + parse->groupingSets == NIL && + agg_costs.numOrderedAggs == 0 && + grouping_is_hashable(parse->groupClause)); + + if (can_hash) + { + Assert(agg_info->group_exprs != NIL); + dNumGroups = estimate_num_groups(root, agg_info->group_exprs, + subpath->rows, NULL); + + hashaggtablesize = estimate_hashagg_tablesize(subpath, &agg_costs, + dNumGroups); + + if (hashaggtablesize < work_mem * 1024L) + { + /* + * qual is NIL because the HAVING clause cannot be evaluated until + * the final value of the aggregate is known. + */ + result = create_agg_path(root, rel, subpath, + target, + AGG_HASHED, + aggsplit, + agg_info->group_clauses, + NIL, + &agg_costs, + dNumGroups); + } + } + + return result; +} + /* * create_groupingsets_path * Creates a pathnode that represents performing GROUPING SETS aggregation diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index c238dd6538..10becc09e9 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -17,18 +17,24 @@ #include #include "miscadmin.h" +#include "catalog/pg_class_d.h" +#include "catalog/pg_constraint.h" #include "optimizer/appendinfo.h" #include "optimizer/clauses.h" #include "optimizer/cost.h" +#include "optimizer/optimizer.h" #include "optimizer/inherit.h" #include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/placeholder.h" #include "optimizer/plancat.h" +#include "optimizer/planner.h" #include "optimizer/restrictinfo.h" #include "optimizer/tlist.h" +#include "parser/parse_oper.h" #include "partitioning/partbounds.h" #include "utils/hsearch.h" +#include "utils/selfuncs.h" typedef struct RelInfoEntry @@ -63,6 +69,9 @@ static void build_child_join_reltarget(PlannerInfo *root, RelOptInfo *childrel, int nappinfos, AppendRelInfo **appinfos); +static bool init_grouping_targets(PlannerInfo *root, RelOptInfo *rel, + PathTarget *target, PathTarget *agg_input, + List *gvis, List **group_exprs_extra_p); /* @@ -351,6 +360,102 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent) return rel; } +/* + * build_simple_grouped_rel + * Construct a new RelOptInfo for a grouped base relation out of an + * existing non-grouped relation. On success, pointer to the corresponding + * RelAggInfo is stored in *agg_info_p in addition to returning the grouped + * relation. + */ +RelOptInfo * +build_simple_grouped_rel(PlannerInfo *root, int relid, + RelAggInfo **agg_info_p) +{ + RangeTblEntry *rte; + RelOptInfo *rel_plain, + *rel_grouped; + RelAggInfo *agg_info; + + /* Isn't there any grouping expression to be pushed down? */ + if (root->grouped_var_list == NIL) + return NULL; + + rel_plain = root->simple_rel_array[relid]; + + /* Caller should only pass rti that represents base relation. */ + Assert(rel_plain != NULL); + + /* + * Not all RTE kinds are supported when grouping is considered. + * + * TODO Consider relaxing some of these restrictions. + */ + rte = root->simple_rte_array[rel_plain->relid]; + if (rte->rtekind != RTE_RELATION || + rte->relkind == RELKIND_FOREIGN_TABLE || + rte->tablesample != NULL) + return NULL; + + /* + * Grouped append relation is not supported yet. + */ + if (rte->inh) + return NULL; + + /* + * Currently we do not support child relations ("other rels"). + */ + if (rel_plain->reloptkind != RELOPT_BASEREL) + return NULL; + + /* + * Prepare the information we need for aggregation of the rel contents. + */ + agg_info = create_rel_agg_info(root, rel_plain); + if (agg_info == NULL) + return NULL; + + /* + * TODO Consider if 1) a flat copy is o.k., 2) it's safer in terms of + * adding new fields to RelOptInfo) to copy everything and then reset some + * fields, or to zero the structure and copy individual fields. + */ + rel_grouped = makeNode(RelOptInfo); + memcpy(rel_grouped, rel_plain, sizeof(RelOptInfo)); + + /* + * Note on consider_startup: while the AGG_HASHED strategy needs the whole + * relation, AGG_SORTED does not. Therefore we do not force + * consider_startup to false. + */ + + /* + * Set the appropriate target for grouped paths. + * + * reltarget should match the target of partially aggregated paths. + */ + rel_grouped->reltarget = agg_info->target; + + /* + * Grouped paths must not be mixed with the plain ones. + */ + rel_grouped->pathlist = NIL; + rel_grouped->partial_pathlist = NIL; + rel_grouped->cheapest_startup_path = NULL; + rel_grouped->cheapest_total_path = NULL; + rel_grouped->cheapest_unique_path = NULL; + rel_grouped->cheapest_parameterized_paths = NIL; + + /* + * The number of aggregation input rows is simply the number of rows of + * the non-grouped relation, which should have been estimated by now. + */ + agg_info->input_rows = rel_plain->rows; + + *agg_info_p = agg_info; + return rel_grouped; +} + /* * find_base_rel * Find a base or other relation entry, which must already exist. @@ -462,8 +567,12 @@ find_rel_info(RelInfoList *list, Relids relids) void *item = lfirst(l); Relids item_relids; - Assert(IsA(item, RelOptInfo)); - item_relids = ((RelOptInfo *) item)->relids; + Assert(IsA(item, RelOptInfo) ||IsA(item, RelAggInfo)); + + if (IsA(item, RelOptInfo)) + item_relids = ((RelOptInfo *) item)->relids; + else if (IsA(item, RelAggInfo)) + item_relids = ((RelAggInfo *) item)->relids; if (bms_equal(item_relids, relids)) return item; @@ -492,7 +601,7 @@ find_join_rel(PlannerInfo *root, Relids relids) static void add_rel_info(RelInfoList *list, void *data) { - Assert(IsA(data, RelOptInfo)); + Assert(IsA(data, RelOptInfo) ||IsA(data, RelAggInfo)); /* GEQO requires us to append the new joinrel to the end of the list! */ list->items = lappend(list->items, data); @@ -504,7 +613,11 @@ add_rel_info(RelInfoList *list, void *data) RelInfoEntry *hentry; bool found; - relids = ((RelOptInfo *) data)->relids; + if (IsA(data, RelOptInfo)) + relids = ((RelOptInfo *) data)->relids; + else if (IsA(data, RelAggInfo)) + relids = ((RelAggInfo *) data)->relids; + hentry = (RelInfoEntry *) hash_search(list->hash, &relids, HASH_ENTER, @@ -525,6 +638,57 @@ add_join_rel(PlannerInfo *root, RelOptInfo *joinrel) add_rel_info(root->join_rel_list, joinrel); } +/* + * add_grouped_rel + * Add grouped base or join relation to the list of grouped relations in + * the given PlannerInfo. Also add the corresponding RelAggInfo to + * agg_info_list. + */ +void +add_grouped_rel(PlannerInfo *root, RelOptInfo *rel, RelAggInfo *agg_info) +{ + add_rel_info(root->grouped_rel_list, rel); + add_rel_info(root->agg_info_list, rel); +} + +/* + * find_grouped_rel + * Returns grouped relation entry (base or join relation) corresponding to + * 'relids' or NULL if none exists. + * + * If agg_info_p is a valid pointer, then pointer to RelAggInfo that + * corresponds to the relation returned is assigned to *agg_info_p. + */ +RelOptInfo * +find_grouped_rel(PlannerInfo *root, Relids relids, RelAggInfo **agg_info_p) +{ + RelOptInfo *rel; + + rel = (RelOptInfo *) find_rel_info(root->grouped_rel_list, relids); + if (rel == NULL) + { + if (agg_info_p) + *agg_info_p = NULL; + + return NULL; + } + + /* Is caller interested in RelAggInfo? */ + if (agg_info_p) + { + RelAggInfo *agg_info; + + agg_info = (RelAggInfo *) find_rel_info(root->agg_info_list, relids); + + /* The relation exists, so the agg_info should be there too. */ + Assert(agg_info != NULL); + + *agg_info_p = agg_info; + } + + return rel; +} + /* * set_foreign_rel_properties * Set up foreign-join fields if outer and inner relation are foreign @@ -587,6 +751,7 @@ set_foreign_rel_properties(RelOptInfo *joinrel, RelOptInfo *outer_rel, * 'restrictlist_ptr': result variable. If not NULL, *restrictlist_ptr * receives the list of RestrictInfo nodes that apply to this * particular pair of joinable relations. + * 'agg_info' indicates that grouped join relation should be created. * * restrictlist_ptr makes the routine's API a little grotty, but it saves * duplicated calculation of the restrictlist... @@ -597,10 +762,12 @@ build_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, RelOptInfo *inner_rel, SpecialJoinInfo *sjinfo, - List **restrictlist_ptr) + List **restrictlist_ptr, + RelAggInfo *agg_info) { RelOptInfo *joinrel; List *restrictlist; + bool grouped = agg_info != NULL; /* This function should be used only for join between parents. */ Assert(!IS_OTHER_REL(outer_rel) && !IS_OTHER_REL(inner_rel)); @@ -608,7 +775,8 @@ build_join_rel(PlannerInfo *root, /* * See if we already have a joinrel for this set of base rels. */ - joinrel = find_join_rel(root, joinrelids); + joinrel = !grouped ? find_join_rel(root, joinrelids) : + find_grouped_rel(root, joinrelids, NULL); if (joinrel) { @@ -700,9 +868,21 @@ build_join_rel(PlannerInfo *root, * and inner rels we first try to build it from. But the contents should * be the same regardless. */ - build_joinrel_tlist(root, joinrel, outer_rel); - build_joinrel_tlist(root, joinrel, inner_rel); - add_placeholders_to_joinrel(root, joinrel, outer_rel, inner_rel); + if (!grouped) + { + joinrel->reltarget = create_empty_pathtarget(); + build_joinrel_tlist(root, joinrel, outer_rel); + build_joinrel_tlist(root, joinrel, inner_rel); + add_placeholders_to_joinrel(root, joinrel, outer_rel, inner_rel); + } + else + { + /* + * The target for grouped join should already have its cost and width + * computed, see create_rel_agg_info(). + */ + joinrel->reltarget = agg_info->target; + } /* * add_placeholders_to_joinrel also took care of adding the ph_lateral @@ -734,49 +914,73 @@ build_join_rel(PlannerInfo *root, joinrel->has_eclass_joins = has_relevant_eclass_joinclause(root, joinrel); /* Store the partition information. */ - build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist, - sjinfo->jointype); - - /* - * Set estimates of the joinrel's size. - */ - set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel, - sjinfo, restrictlist); - - /* - * Set the consider_parallel flag if this joinrel could potentially be - * scanned within a parallel worker. If this flag is false for either - * inner_rel or outer_rel, then it must be false for the joinrel also. - * Even if both are true, there might be parallel-restricted expressions - * in the targetlist or quals. - * - * Note that if there are more than two rels in this relation, they could - * be divided between inner_rel and outer_rel in any arbitrary way. We - * assume this doesn't matter, because we should hit all the same baserels - * and joinclauses while building up to this joinrel no matter which we - * take; therefore, we should make the same decision here however we get - * here. - */ - if (inner_rel->consider_parallel && outer_rel->consider_parallel && - is_parallel_safe(root, (Node *) restrictlist) && - is_parallel_safe(root, (Node *) joinrel->reltarget->exprs)) - joinrel->consider_parallel = true; + if (!grouped) + build_joinrel_partition_info(joinrel, outer_rel, inner_rel, + restrictlist, sjinfo->jointype); /* Add the joinrel to the PlannerInfo. */ - add_join_rel(root, joinrel); + if (!grouped) + add_join_rel(root, joinrel); + else + add_grouped_rel(root, joinrel, agg_info); /* - * Also, if dynamic-programming join search is active, add the new joinrel - * to the appropriate sublist. Note: you might think the Assert on number - * of members should be for equality, but some of the level 1 rels might - * have been joinrels already, so we can only assert <=. + * Also, if dynamic-programming join search is active, add the new + * joinrelset to the appropriate sublist. Note: you might think the + * Assert on number of members should be for equality, but some of the + * level 1 rels might have been joinrels already, so we can only assert + * <=. + * + * Do noting for grouped relation as it's stored aside from + * join_rel_level. */ - if (root->join_rel_level) + if (root->join_rel_level && !grouped) { Assert(root->join_cur_level > 0); - Assert(root->join_cur_level <= bms_num_members(joinrel->relids)); + Assert(root->join_cur_level <= bms_num_members(joinrelids)); root->join_rel_level[root->join_cur_level] = - lappend(root->join_rel_level[root->join_cur_level], joinrel); + lappend(root->join_rel_level[root->join_cur_level], + joinrel); + } + + /* Set estimates of the joinrel's size. */ + if (!grouped) + { + set_joinrel_size_estimates(root, joinrel, outer_rel, inner_rel, + sjinfo, restrictlist); + + /* + * Set the consider_parallel flag if this joinrel could potentially be + * scanned within a parallel worker. If this flag is false for either + * inner_rel or outer_rel, then it must be false for the joinrel also. + * Even if both are true, there might be parallel-restricted + * expressions in the targetlist or quals. + * + * Note that if there are more than two rels in this relation, they + * could be divided between inner_rel and outer_rel in any arbitrary + * way. We assume this doesn't matter, because we should hit all the + * same baserels and joinclauses while building up to this joinrel no + * matter which we take; therefore, we should make the same decision + * here however we get here. + */ + if (inner_rel->consider_parallel && outer_rel->consider_parallel && + is_parallel_safe(root, (Node *) restrictlist) && + is_parallel_safe(root, (Node *) joinrel->reltarget->exprs)) + joinrel->consider_parallel = true; + } + else + { + /* + * Grouping essentially changes the number of rows. + * + * XXX We do not distinguish whether two plain rels are joined and the + * result is aggregated, or the aggregation has been already applied + * to one of the input rels. Is this worth extra effort, e.g. + * maintaining a separate RelOptInfo for each case (one difficulty + * that would introduce is construction of AppendPath)? + */ + joinrel->rows = estimate_num_groups(root, agg_info->group_exprs, + agg_info->input_rows, NULL); } return joinrel; @@ -1820,3 +2024,625 @@ build_child_join_reltarget(PlannerInfo *root, childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple; childrel->reltarget->width = parentrel->reltarget->width; } + +/* + * Check if the relation can produce grouped paths and return the information + * it'll need for it. The passed relation is the non-grouped one which has the + * reltarget already constructed. + */ +RelAggInfo * +create_rel_agg_info(PlannerInfo *root, RelOptInfo *rel) +{ + List *gvis; + List *aggregates = NIL; + bool found_other_rel_agg; + ListCell *lc; + RelAggInfo *result; + PathTarget *agg_input; + PathTarget *target = NULL; + List *grp_exprs_extra = NIL; + List *group_clauses_final; + int i; + + /* + * The function shouldn't have been called if there's no opportunity for + * aggregation push-down. + */ + Assert(root->grouped_var_list != NIL); + + result = makeNode(RelAggInfo); + + /* + * The current implementation of aggregation push-down cannot handle + * PlaceHolderVar (PHV). + * + * If we knew that the PHV should be evaluated in this target (and of + * course, if its expression matched some Aggref argument), we'd just let + * init_grouping_targets add that Aggref. On the other hand, if we knew + * that the PHV is evaluated below the current rel, we could ignore it + * because the referencing Aggref would take care of propagation of the + * value to upper joins. + * + * The problem is that the same PHV can be evaluated in the target of the + * current rel or in that of lower rel --- depending on the input paths. + * For example, consider rel->relids = {A, B, C} and if ph_eval_at = {B, + * C}. Path "A JOIN (B JOIN C)" implies that the PHV is evaluated by the + * "(B JOIN C)", while path "(A JOIN B) JOIN C" evaluates the PHV itself. + */ + foreach(lc, rel->reltarget->exprs) + { + Expr *expr = lfirst(lc); + + if (IsA(expr, PlaceHolderVar)) + return NULL; + } + + if (IS_SIMPLE_REL(rel)) + { + RangeTblEntry *rte = root->simple_rte_array[rel->relid];; + + /* + * rtekind != RTE_RELATION case is not supported yet. + */ + if (rte->rtekind != RTE_RELATION) + return NULL; + } + + /* Caller should only pass base relations or joins. */ + Assert(rel->reloptkind == RELOPT_BASEREL || + rel->reloptkind == RELOPT_JOINREL); + + /* + * If any outer join can set the attribute value to NULL, the Agg plan + * would receive different input at the base rel level. + * + * XXX For RELOPT_JOINREL, do not return if all the joins that can set any + * entry of the grouped target (do we need to postpone this check until + * the grouped target is available, and init_grouping_targets take care?) + * of this rel to NULL are provably below rel. (It's ok if rel is one of + * these joins.) + */ + if (bms_overlap(rel->relids, root->nullable_baserels)) + return NULL; + + /* + * Use equivalence classes to generate additional grouping expressions for + * the current rel. Without these we might not be able to apply + * aggregation to the relation result set. + * + * It's important that create_grouping_expr_grouped_var_infos has + * processed the explicit grouping columns by now. If the grouping clause + * contains multiple expressions belonging to the same EC, the original + * (i.e. not derived) one should be preferred when we build grouping + * target for a relation. Otherwise we have a problem when trying to match + * target entries to grouping clauses during plan creation, see + * get_grouping_expression(). + */ + gvis = list_copy(root->grouped_var_list); + foreach(lc, root->grouped_var_list) + { + GroupedVarInfo *gvi = lfirst_node(GroupedVarInfo, lc); + int relid = -1; + + /* Only interested in grouping expressions. */ + if (IsA(gvi->gvexpr, Aggref)) + continue; + + while ((relid = bms_next_member(rel->relids, relid)) >= 0) + { + GroupedVarInfo *gvi_trans; + + gvi_trans = translate_expression_to_rels(root, gvi, relid); + if (gvi_trans != NULL) + gvis = lappend(gvis, gvi_trans); + } + } + + /* + * Check if some aggregates or grouping expressions can be evaluated in + * this relation's target, and collect all vars referenced by these + * aggregates / grouping expressions; + */ + found_other_rel_agg = false; + foreach(lc, gvis) + { + GroupedVarInfo *gvi = lfirst_node(GroupedVarInfo, lc); + + /* + * The subset includes gv_eval_at uninitialized, which includes + * Aggref.aggstar. + */ + if (bms_is_subset(gvi->gv_eval_at, rel->relids)) + { + /* + * init_grouping_targets will handle plain Var grouping + * expressions because it needs to look them up in + * grouped_var_list anyway. + */ + if (IsA(gvi->gvexpr, Var)) + continue; + + /* + * Currently, GroupedVarInfo only handles Vars and Aggrefs. + */ + Assert(IsA(gvi->gvexpr, Aggref)); + + /* We only derive grouped expressions using ECs, not aggregates */ + Assert(!gvi->derived); + + gvi->agg_partial = (Aggref *) copyObject(gvi->gvexpr); + mark_partial_aggref(gvi->agg_partial, AGGSPLIT_INITIAL_SERIAL); + + /* + * Accept the aggregate. + */ + aggregates = lappend(aggregates, gvi); + } + else if (IsA(gvi->gvexpr, Aggref)) + { + /* + * Remember that there is at least one aggregate expression that + * needs something else than this rel. + */ + found_other_rel_agg = true; + + /* + * This condition effectively terminates creation of the + * RelAggInfo, so there's no reason to check the next + * GroupedVarInfo. + */ + break; + } + } + + /* + * Grouping makes little sense w/o aggregate function and w/o grouping + * expressions. + */ + if (aggregates == NIL) + { + list_free(gvis); + return NULL; + } + + /* + * Give up if some other aggregate(s) need relations other than the + * current one. + * + * If the aggregate needs the current rel plus anything else, then the + * problem is that grouping of the current relation could make some input + * variables unavailable for the "higher aggregate", and it'd also + * decrease the number of input rows the "higher aggregate" receives. + * + * If the aggregate does not even need the current rel, then neither the + * current rel nor anything else should be grouped because we do not + * support join of two grouped relations. + */ + if (found_other_rel_agg) + { + list_free(gvis); + return NULL; + } + + /* + * Create target for grouped paths as well as one for the input paths of + * the aggregation paths. + */ + target = create_empty_pathtarget(); + agg_input = create_empty_pathtarget(); + + /* + * Cannot suitable targets for the aggregation push-down be derived? + */ + if (!init_grouping_targets(root, rel, target, agg_input, gvis, + &grp_exprs_extra)) + { + list_free(gvis); + return NULL; + } + + list_free(gvis); + + /* + * Aggregation push-down makes no sense w/o grouping expressions. + */ + if ((list_length(target->exprs) + list_length(grp_exprs_extra)) == 0) + return NULL; + + group_clauses_final = root->parse->groupClause; + + /* + * If the aggregation target should have extra grouping expressions (in + * order to emit input vars for join conditions), add them now. This step + * includes assignment of tleSortGroupRef's which we can generate now. + */ + if (list_length(grp_exprs_extra) > 0) + { + Index sortgroupref; + + /* + * We'll have to add some clauses, but query group clause must be + * preserved. + */ + group_clauses_final = list_copy(group_clauses_final); + + /* + * Always start at root->max_sortgroupref. The extra grouping + * expressions aren't used during the final aggregation, so the + * sortgroupref values don't need to be unique across the query. Thus + * we don't have to increase root->max_sortgroupref, which makes + * recognition of the extra grouping expressions pretty easy. + */ + sortgroupref = root->max_sortgroupref; + + /* + * Generate the SortGroupClause's and add the expressions to the + * target. + */ + foreach(lc, grp_exprs_extra) + { + Var *var = lfirst_node(Var, lc); + SortGroupClause *cl = makeNode(SortGroupClause); + + /* + * Initialize the SortGroupClause. + * + * As the final aggregation will not use this grouping expression, + * we don't care whether sortop is < or >. The value of + * nulls_first should not matter for the same reason. + */ + cl->tleSortGroupRef = ++sortgroupref; + get_sort_group_operators(var->vartype, + false, true, false, + &cl->sortop, &cl->eqop, NULL, + &cl->hashable); + group_clauses_final = lappend(group_clauses_final, cl); + add_column_to_pathtarget(target, (Expr *) var, + cl->tleSortGroupRef); + + /* + * The aggregation input target must emit this var too. + */ + add_column_to_pathtarget(agg_input, (Expr *) var, + cl->tleSortGroupRef); + } + } + + /* + * Add aggregates to the grouping target. + */ + add_aggregates_to_target(root, target, aggregates); + + /* + * Build a list of grouping expressions and a list of the corresponding + * SortGroupClauses. + */ + i = 0; + foreach(lc, target->exprs) + { + Index sortgroupref = 0; + SortGroupClause *cl; + Expr *texpr; + + texpr = (Expr *) lfirst(lc); + + if (IsA(texpr, Aggref)) + { + /* + * Once we see Aggref, no grouping expressions should follow. + */ + break; + } + + /* + * Find the clause by sortgroupref. + */ + sortgroupref = target->sortgrouprefs[i++]; + + /* + * Besides being an aggregate, the target expression should have no + * other reason then being a column of a relation functionally + * dependent on the GROUP BY clause. So it's not actually a grouping + * column. + */ + if (sortgroupref == 0) + continue; + + /* + * group_clause_final contains the "local" clauses, so this search + * should succeed. + */ + cl = get_sortgroupref_clause(sortgroupref, group_clauses_final); + + result->group_clauses = list_append_unique(result->group_clauses, + cl); + + /* + * Add only unique clauses because of joins (both sides of a join can + * point at the same grouping clause). XXX Is it worth adding a bool + * argument indicating that we're dealing with join right now? + */ + result->group_exprs = list_append_unique(result->group_exprs, + texpr); + } + + /* + * Since neither target nor agg_input is supposed to be identical to the + * source reltarget, compute the width and cost again. + * + * target does not yet contain aggregates, but these will be accounted by + * AggPath. + */ + set_pathtarget_cost_width(root, target); + set_pathtarget_cost_width(root, agg_input); + + result->relids = bms_copy(rel->relids); + result->target = target; + result->agg_input = agg_input; + + /* Finally collect the aggregates. */ + while (lc != NULL) + { + Aggref *aggref = lfirst_node(Aggref, lc); + + /* + * Partial aggregation is what the grouped paths should do. + */ + result->agg_exprs = lappend(result->agg_exprs, aggref); + lc = lnext(lc); + } + + /* The "input_rows" field should be set by caller. */ + return result; +} + +/* + * Initialize target for grouped paths (target) as well as a target for paths + * that generate input for aggregation (agg_input). + * + * group_exprs_extra_p receives a list of Var nodes for which we need to + * construct SortGroupClause. Those vars will then be used as additional + * grouping expressions, for the sake of join clauses. + * + * gvis a list of GroupedVarInfo's possibly useful for rel. + * + * Return true iff the targets could be initialized. + */ +static bool +init_grouping_targets(PlannerInfo *root, RelOptInfo *rel, + PathTarget *target, PathTarget *agg_input, + List *gvis, List **group_exprs_extra_p) +{ + ListCell *lc1, + *lc2; + List *unresolved = NIL; + List *unresolved_sortgrouprefs = NIL; + + foreach(lc1, rel->reltarget->exprs) + { + Var *tvar; + bool is_grouping; + Index sortgroupref = 0; + bool derived = false; + bool needed_by_aggregate; + + /* + * Given that PlaceHolderVar currently prevents us from doing + * aggregation push-down, the source target cannot contain anything + * more complex than a Var. + */ + tvar = lfirst_node(Var, lc1); + + is_grouping = is_grouping_expression(gvis, (Expr *) tvar, + &sortgroupref, &derived); + + /* + * Derived grouping expressions should not be referenced by the query + * targetlist, so let them fall into vars_unresolved. It'll be checked + * later if the current targetlist needs them. For example, we should + * not automatically use Var as a grouping expression if the only + * reason for it to be in the plain relation target is that it's + * referenced by aggregate argument, and it happens to be in the same + * EC as any grouping expression. + */ + if (is_grouping && !derived) + { + Assert(sortgroupref > 0); + + /* + * It's o.k. to use the target expression for grouping. + */ + add_column_to_pathtarget(target, (Expr *) tvar, sortgroupref); + + /* + * As for agg_input, add the original expression but set + * sortgroupref in addition. + */ + add_column_to_pathtarget(agg_input, (Expr *) tvar, sortgroupref); + + /* Process the next expression. */ + continue; + } + + /* + * Is this Var needed in the query targetlist for anything else than + * aggregate input? + */ + needed_by_aggregate = false; + foreach(lc2, root->grouped_var_list) + { + GroupedVarInfo *gvi = lfirst_node(GroupedVarInfo, lc2); + ListCell *lc3; + List *vars; + + if (!IsA(gvi->gvexpr, Aggref)) + continue; + + if (!bms_is_member(tvar->varno, gvi->gv_eval_at)) + continue; + + /* + * XXX Consider some sort of caching. + */ + vars = pull_var_clause((Node *) gvi->gvexpr, PVC_RECURSE_AGGREGATES); + foreach(lc3, vars) + { + Var *var = lfirst_node(Var, lc3); + + if (equal(var, tvar)) + { + needed_by_aggregate = true; + break; + } + } + list_free(vars); + if (needed_by_aggregate) + break; + } + + if (needed_by_aggregate) + { + bool found = false; + + foreach(lc2, root->processed_tlist) + { + TargetEntry *te = lfirst_node(TargetEntry, lc2); + + if (IsA(te->expr, Aggref)) + continue; + + if (equal(te->expr, tvar)) + { + found = true; + break; + } + } + + /* + * If it's only Aggref input, add it to the aggregation input + * target and that's it. + */ + if (!found) + { + add_new_column_to_pathtarget(agg_input, (Expr *) tvar); + continue; + } + } + + /* + * Further investigation involves dependency check, for which we need + * to have all the (plain-var) grouping expressions gathered. + */ + unresolved = lappend(unresolved, tvar); + unresolved_sortgrouprefs = lappend_int(unresolved_sortgrouprefs, + sortgroupref); + } + + /* + * Check for other possible reasons for the var to be in the plain target. + */ + forboth(lc1, unresolved, lc2, unresolved_sortgrouprefs) + { + Var *var = lfirst_node(Var, lc1); + Index sortgroupref = lfirst_int(lc2); + RangeTblEntry *rte; + List *deps = NIL; + Relids relids_subtract; + int ndx; + RelOptInfo *baserel; + + rte = root->simple_rte_array[var->varno]; + + /* + * Check if the Var can be in the grouping key even though it's not + * mentioned by the GROUP BY clause (and could not be derived using + * ECs). + */ + if (sortgroupref == 0 && + check_functional_grouping(rte->relid, var->varno, + var->varlevelsup, + target->exprs, &deps)) + { + /* + * The var shouldn't be actually used as a grouping key (instead, + * the one this depends on will be), so sortgroupref should not be + * important. + */ + add_new_column_to_pathtarget(target, (Expr *) var); + add_new_column_to_pathtarget(agg_input, (Expr *) var); + + /* + * The var may or may not be present in generic grouping + * expression(s) in addition, but this is handled elsewhere. + */ + continue; + } + + /* + * Isn't the expression needed by joins above the current rel? + * + * The relids we're not interested in do include 0, which is the + * top-level targetlist. The only reason for relids to contain 0 + * should be that arg_var is referenced either by aggregate or by + * grouping expression, but right now we're interested in the *other* + * reasons. (As soon aggregation is pushed down, the aggregates in the + * query targetlist no longer need direct reference to arg_var + * anyway.) + */ + relids_subtract = bms_copy(rel->relids); + bms_add_member(relids_subtract, 0); + + baserel = find_base_rel(root, var->varno); + ndx = var->varattno - baserel->min_attr; + if (bms_nonempty_difference(baserel->attr_needed[ndx], + relids_subtract)) + { + /* + * The variable is needed by a join involving this relation. That + * case includes variable that is referenced by a generic grouping + * expression. + * + * The only way to bring this var to the aggregation output is to + * add it to the grouping expressions too. + */ + if (sortgroupref > 0) + { + /* + * The var could be recognized as a potentially useful + * grouping expression at the top of the loop, so we can add + * it to the grouping target, as well as to the agg_input. + */ + add_column_to_pathtarget(target, (Expr *) var, sortgroupref); + add_column_to_pathtarget(agg_input, (Expr *) var, sortgroupref); + } + else + { + /* + * Since root->parse->groupClause is not supposed to contain + * this expression, we need to construct special + * SortGroupClause. Its tleSortGroupRef needs to be unique + * within target_agg, so postpone creation of the + * SortGroupRefs until we're done with the iteration of + * rel->reltarget->exprs. + */ + *group_exprs_extra_p = lappend(*group_exprs_extra_p, var); + } + } + else + { + /* + * As long as the query is semantically correct, arriving here + * means that the var is referenced by a generic grouping + * expression but not referenced by any join. + * + * create_rel_agg_info() should add this variable to "agg_input" + * target and also add the whole generic expression to "target", + * but that's subject to future enhancement of the aggregate + * push-down feature. + */ + return false; + } + } + + return true; +} diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c index 41402821a1..c7105dfc2a 100644 --- a/src/backend/optimizer/util/tlist.c +++ b/src/backend/optimizer/util/tlist.c @@ -826,6 +826,62 @@ apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target) } } +/* + * For each aggregate grouping expression or aggregate to the grouped target. + * + * Caller passes the expressions in the form of GroupedVarInfos so that we + * don't have to look for gvid. + */ +void +add_aggregates_to_target(PlannerInfo *root, PathTarget *target, + List *expressions) +{ + ListCell *lc; + + /* Create the vars and add them to the target. */ + foreach(lc, expressions) + { + GroupedVarInfo *gvi; + + gvi = lfirst_node(GroupedVarInfo, lc); + add_column_to_pathtarget(target, (Expr *) gvi->agg_partial, + gvi->sortgroupref); + } +} + +/* + * Find out if expr can be used as grouping expression in reltarget. + * + * sortgroupref and is_derived reflect the ->sortgroupref and ->derived fields + * of the corresponding GroupedVarInfo. + */ +bool +is_grouping_expression(List *gvis, Expr *expr, Index *sortgroupref, + bool *is_derived) +{ + ListCell *lc; + + foreach(lc, gvis) + { + GroupedVarInfo *gvi = lfirst_node(GroupedVarInfo, lc); + + if (IsA(gvi->gvexpr, Aggref)) + continue; + + if (equal(gvi->gvexpr, expr)) + { + Assert(gvi->sortgroupref > 0); + + *sortgroupref = gvi->sortgroupref; + *is_derived = gvi->derived; + return true; + } + } + + /* The expression cannot be used as grouping key. */ + return false; +} + /* * split_pathtarget_at_srfs * Split given PathTarget into multiple levels to position SRFs safely diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index fc463601ff..8c1929c758 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -1021,6 +1021,15 @@ static struct config_bool ConfigureNamesBool[] = false, NULL, NULL, NULL }, + { + {"enable_agg_pushdown", PGC_USERSET, QUERY_TUNING_METHOD, + gettext_noop("Enables aggregation push-down."), + NULL + }, + &enable_agg_pushdown, + false, + NULL, NULL, NULL + }, { {"enable_parallel_append", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of parallel append plans."), diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 11027cdb10..25d058ef2a 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -224,6 +224,7 @@ typedef enum NodeTag T_IndexOptInfo, T_ForeignKeyOptInfo, T_ParamPathInfo, + T_RelAggInfo, T_Path, T_IndexPath, T_BitmapHeapPath, @@ -268,6 +269,7 @@ typedef enum NodeTag T_SpecialJoinInfo, T_AppendRelInfo, T_PlaceHolderInfo, + T_GroupedVarInfo, T_MinMaxAggInfo, T_PlannerParamItem, T_RollupData, diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 38dc186623..988d6220c8 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -240,6 +240,23 @@ struct PlannerInfo */ struct RelInfoList *join_rel_list; /* list of join-relation RelOptInfos */ + /* + * grouped_rel_list is a list of RelOptInfos that represent grouped + * relations, both base relations and joins. Unlike join_rel_list, base + * relations are accepted because grouped base relation is of rather + * limited scope, i.e. it's only needed during join search. Thus it does + * not deserve separate storage like simple_rel_array. + */ + struct RelInfoList *grouped_rel_list; /* list of grouped relation + * RelOptInfos */ + + /* + * agg_info_list contains one instance of RelAggInfo per an item of + * grouped_rel_list. + */ + struct RelInfoList *agg_info_list; /* list of grouped relation + * RelAggInfos */ + /* * When doing a dynamic-programming-style join search, join_rel_level[k] * is a list of all join-relation RelOptInfos of level k, and @@ -285,6 +302,8 @@ struct PlannerInfo List *placeholder_list; /* list of PlaceHolderInfos */ + List *grouped_var_list; /* List of GroupedVarInfos. */ + List *fkey_list; /* list of ForeignKeyOptInfos */ List *query_pathkeys; /* desired pathkeys for query_planner() */ @@ -316,6 +335,12 @@ struct PlannerInfo */ List *processed_tlist; + /* + * The maximum ressortgroupref among target entries in processed_list. + * Useful when adding extra grouping expressions for partial aggregation. + */ + int max_sortgroupref; + /* Fields filled during create_plan() for use in setrefs.c */ AttrNumber *grouping_map; /* for GroupingFunc fixup */ List *minmax_aggs; /* List of MinMaxAggInfos */ @@ -754,6 +779,60 @@ typedef struct RelInfoList struct HTAB *hash; } RelInfoList; +/* + * RelAggInfo + * Information needed to create grouped paths for base rels and joins. + * + * "relids" is the set of base-relation identifiers, just like with + * RelOptInfo. + * + * "target" will be used as pathtarget if partial aggregation is applied to + * base relation or join. The same target will also --- if the relation is a + * join --- be used to joinin grouped path to a non-grouped one. This target + * can contain plain-Var grouping expressions and Aggref nodes. + * + * Note: There's a convention that Aggref expressions are supposed to follow + * the other expressions of the target. Iterations of ->exprs may rely on this + * arrangement. + * + * "agg_input" contains Vars used either as grouping expressions or aggregate + * arguments. Paths providing the aggregation plan with input data should use + * this target. The only difference from reltarget of the non-grouped relation + * is that some items can have sortgroupref initialized. + * + * "input_rows" is the estimated number of input rows for AggPath. It's + * actually just a workspace for users of the structure, i.e. not initialized + * when instance of the structure is created. + * + * "group_clauses" and "group_exprs" are lists of SortGroupClause and the + * corresponding grouping expressions respectively. + * + * "agg_exprs" is a list of Aggref nodes for the aggregation of the relation's + * paths. + * + * "rel_grouped" is the relation containing the partially aggregated paths. + */ +typedef struct RelAggInfo +{ + NodeTag type; + + Relids relids; /* Base rels contained in this grouped rel. */ + + struct PathTarget *target; /* Target for grouped paths. */ + + struct PathTarget *agg_input; /* pathtarget of paths that generate input + * for aggregation paths. */ + + double input_rows; + + List *group_clauses; + List *group_exprs; + + List *agg_exprs; /* Aggref expressions. */ + + RelOptInfo *rel_grouped; /* Grouped relation. */ +} RelAggInfo; + /* * IndexOptInfo * Per-index information for planning/optimization @@ -2272,6 +2351,26 @@ typedef struct PlaceHolderInfo int32 ph_width; /* estimated attribute width */ } PlaceHolderInfo; +/* + * GroupedVarInfo exists for each expression that can be used as an aggregate + * or grouping expression evaluated below a join. + */ +typedef struct GroupedVarInfo +{ + NodeTag type; + + Expr *gvexpr; /* the represented expression. */ + Aggref *agg_partial; /* if gvexpr is aggregate, agg_partial is the + * corresponding partial aggregate */ + Index sortgroupref; /* If gvexpr is a grouping expression, this is + * the tleSortGroupRef of the corresponding + * SortGroupClause. */ + Relids gv_eval_at; /* lowest level we can evaluate the expression + * at or NULL if it can happen anywhere. */ + bool derived; /* derived from another GroupedVarInfo using + * equeivalence classes? */ +} GroupedVarInfo; + /* * This struct describes one potentially index-optimizable MIN/MAX aggregate * function. MinMaxAggPath contains a list of these, and if we accept that diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index 2f9aeec4a7..90f6995dc2 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -55,4 +55,6 @@ extern void CommuteOpExpr(OpExpr *clause); extern Query *inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte); +extern GroupedVarInfo *translate_expression_to_rels(PlannerInfo *root, + GroupedVarInfo *gvi, Index relid); #endif /* CLAUSES_H */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 182ffeef4b..2d05ff5a15 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -210,6 +210,14 @@ extern AggPath *create_agg_path(PlannerInfo *root, List *qual, const AggClauseCosts *aggcosts, double numGroups); +extern AggPath *create_agg_sorted_path(PlannerInfo *root, + RelOptInfo *rel, + Path *subpath, + RelAggInfo *agg_info); +extern AggPath *create_agg_hashed_path(PlannerInfo *root, + RelOptInfo *rel, + Path *subpath, + RelAggInfo *agg_info); extern GroupingSetsPath *create_groupingsets_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, @@ -281,14 +289,21 @@ extern void setup_append_rel_array(PlannerInfo *root); extern void expand_planner_arrays(PlannerInfo *root, int add_size); extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent); +extern RelOptInfo *build_simple_grouped_rel(PlannerInfo *root, int relid, + RelAggInfo **agg_info_p); extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid); extern RelOptInfo *find_join_rel(PlannerInfo *root, Relids relids); +extern void add_grouped_rel(PlannerInfo *root, RelOptInfo *rel, + RelAggInfo *agg_info); +extern RelOptInfo *find_grouped_rel(PlannerInfo *root, Relids relids, + RelAggInfo **agg_info_p); extern RelOptInfo *build_join_rel(PlannerInfo *root, Relids joinrelids, RelOptInfo *outer_rel, RelOptInfo *inner_rel, SpecialJoinInfo *sjinfo, - List **restrictlist_ptr); + List **restrictlist_ptr, + RelAggInfo *agg_info); extern Relids min_join_parameterization(PlannerInfo *root, Relids joinrelids, RelOptInfo *outer_rel, @@ -314,5 +329,5 @@ extern RelOptInfo *build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel, RelOptInfo *inner_rel, RelOptInfo *parent_joinrel, List *restrictlist, SpecialJoinInfo *sjinfo, JoinType jointype); - +extern RelAggInfo *create_rel_agg_info(PlannerInfo *root, RelOptInfo *rel); #endif /* PATHNODE_H */ diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 7345137d1d..3006ebf5aa 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -21,6 +21,7 @@ * allpaths.c */ extern PGDLLIMPORT bool enable_geqo; +extern PGDLLIMPORT bool enable_agg_pushdown; extern PGDLLIMPORT int geqo_threshold; extern PGDLLIMPORT int min_parallel_table_scan_size; extern PGDLLIMPORT int min_parallel_index_scan_size; @@ -52,8 +53,14 @@ extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist); extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels); + extern void generate_gather_paths(PlannerInfo *root, RelOptInfo *rel, bool override_rows); +extern void generate_grouping_paths(PlannerInfo *root, + RelOptInfo *rel_grouped, + RelOptInfo *rel_plain, + RelAggInfo *agg_info); + extern int compute_parallel_worker(RelOptInfo *rel, double heap_pages, double index_pages, int max_workers); extern void create_partial_bitmap_paths(PlannerInfo *root, RelOptInfo *rel, diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index e7aaddd50d..761f610a96 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -28,7 +28,8 @@ typedef void (*query_pathkeys_callback) (PlannerInfo *root, void *extra); * prototypes for plan/planmain.c */ extern RelOptInfo *query_planner(PlannerInfo *root, - query_pathkeys_callback qp_callback, void *qp_extra); + query_pathkeys_callback qp_callback, void *qp_extra, + RelOptInfo **final_rel_grouped_p); /* * prototypes for plan/planagg.c @@ -69,6 +70,7 @@ extern void add_other_rels_to_query(PlannerInfo *root); extern void build_base_rel_tlists(PlannerInfo *root, List *final_tlist); extern void add_vars_to_targetlist(PlannerInfo *root, List *vars, Relids where_needed, bool create_new_ph); +extern void setup_aggregate_pushdown(PlannerInfo *root); extern void find_lateral_references(PlannerInfo *root); extern void create_lateral_join_info(PlannerInfo *root); extern List *deconstruct_jointree(PlannerInfo *root); diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h index 8a2378f968..e48f37bac2 100644 --- a/src/include/optimizer/tlist.h +++ b/src/include/optimizer/tlist.h @@ -50,8 +50,14 @@ extern void split_pathtarget_at_srfs(PlannerInfo *root, PathTarget *target, PathTarget *input_target, List **targets, List **targets_contain_srfs); +/* TODO Find the best location (position and in some cases even file) for the + * following ones. */ +extern void add_aggregates_to_target(PlannerInfo *root, PathTarget *target, + List *expressions); +extern bool is_grouping_expression(List *gvis, Expr *expr, + Index *sortgroupref, bool *is_derived); + /* Convenience macro to get a PathTarget with valid cost/width fields */ #define create_pathtarget(root, tlist) \ set_pathtarget_cost_width(root, make_pathtarget_from_tlist(tlist)) - #endif /* TLIST_H */ diff --git a/src/test/regress/sql/agg_pushdown.sql b/src/test/regress/sql/agg_pushdown.sql new file mode 100644 index 0000000000..6cf2ed9c20 --- /dev/null +++ b/src/test/regress/sql/agg_pushdown.sql @@ -0,0 +1,117 @@ +BEGIN; + +CREATE TABLE agg_pushdown_parent ( + i int primary key, + x int); + +CREATE TABLE agg_pushdown_child1 ( + j int, + parent int references agg_pushdown_parent, + v double precision, + PRIMARY KEY (j, parent)); + +CREATE INDEX ON agg_pushdown_child1(parent); + +CREATE TABLE agg_pushdown_child2 ( + k int, + parent int references agg_pushdown_parent, + v double precision, + PRIMARY KEY (k, parent));; + +INSERT INTO agg_pushdown_parent(i, x) +SELECT n, n +FROM generate_series(0, 7) AS s(n); + +INSERT INTO agg_pushdown_child1(j, parent, v) +SELECT 128 * i + n, i, random() +FROM generate_series(0, 127) AS s(n), agg_pushdown_parent; + +INSERT INTO agg_pushdown_child2(k, parent, v) +SELECT 128 * i + n, i, random() +FROM generate_series(0, 127) AS s(n), agg_pushdown_parent; + +COMMIT; +ANALYZE; + +SET enable_agg_pushdown TO on; + +SET enable_nestloop TO on; +SET enable_hashjoin TO off; +SET enable_mergejoin TO off; + +-- Perform scan of a table, aggregate the result, join it to the other table +-- and finalize the aggregation. +-- +-- In addition, check that functionally dependent column "c.x" can be +-- referenced by SELECT although GROUP BY references "p.i". +EXPLAIN (COSTS off) +SELECT p.x, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + +-- The same for hash join. +SET enable_nestloop TO off; +SET enable_hashjoin TO on; + +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + +-- The same for merge join. +SET enable_hashjoin TO off; +SET enable_mergejoin TO on; + +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + +SET enable_nestloop TO on; +SET enable_hashjoin TO on; + +-- Scan index on agg_pushdown_child1(parent) column and aggregate the result +-- using AGG_SORTED strategy. +SET enable_seqscan TO off; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + +SET enable_seqscan TO on; + +-- Join "c1" to "p.x" column, i.e. one that is not in the GROUP BY clause. The +-- planner should still use "c1.parent" as grouping expression for partial +-- aggregation, although it's not in the same equivalence class as the GROUP +-- BY expression ("p.i"). The reason to use "c1.parent" for partial +-- aggregation is that this is the only way for "c1" to provide the join +-- expression with input data. +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.x GROUP BY p.i; + +-- Perform nestloop join between agg_pushdown_child1 and agg_pushdown_child2 +-- and aggregate the result. +SET enable_nestloop TO on; +SET enable_hashjoin TO off; +SET enable_mergejoin TO off; + +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN +agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON +c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i; + +-- The same for hash join. +SET enable_nestloop TO off; +SET enable_hashjoin TO on; + +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN +agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON +c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i; + +-- The same for merge join. +SET enable_hashjoin TO off; +SET enable_mergejoin TO on; +SET enable_seqscan TO off; + +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN +agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON +c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i; diff --git a/src/test/regress/expected/agg_pushdown.out b/src/test/regress/expected/agg_pushdown.out new file mode 100644 index 0000000000..b3a97f86d6 --- /dev/null +++ b/src/test/regress/expected/agg_pushdown.out @@ -0,0 +1,217 @@ +BEGIN; +CREATE TABLE agg_pushdown_parent ( + i int primary key, + x int); +CREATE TABLE agg_pushdown_child1 ( + j int, + parent int references agg_pushdown_parent, + v double precision, + PRIMARY KEY (j, parent)); +CREATE INDEX ON agg_pushdown_child1(parent); +CREATE TABLE agg_pushdown_child2 ( + k int, + parent int references agg_pushdown_parent, + v double precision, + PRIMARY KEY (k, parent));; +INSERT INTO agg_pushdown_parent(i, x) +SELECT n, n +FROM generate_series(0, 7) AS s(n); +INSERT INTO agg_pushdown_child1(j, parent, v) +SELECT 128 * i + n, i, random() +FROM generate_series(0, 127) AS s(n), agg_pushdown_parent; +INSERT INTO agg_pushdown_child2(k, parent, v) +SELECT 128 * i + n, i, random() +FROM generate_series(0, 127) AS s(n), agg_pushdown_parent; +COMMIT; +ANALYZE; +SET enable_agg_pushdown TO on; +SET enable_nestloop TO on; +SET enable_hashjoin TO off; +SET enable_mergejoin TO off; +-- Perform scan of a table, aggregate the result, join it to the other table +-- and finalize the aggregation. +-- +-- In addition, check that functionally dependent column "c.x" can be +-- referenced by SELECT although GROUP BY references "p.i". +EXPLAIN (COSTS off) +SELECT p.x, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + QUERY PLAN +-------------------------------------------------------------------------------------- + Finalize GroupAggregate + Group Key: p.i + -> Sort + Sort Key: p.i + -> Nested Loop + -> Partial HashAggregate + Group Key: c1.parent + -> Seq Scan on agg_pushdown_child1 c1 + -> Index Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p + Index Cond: (i = c1.parent) +(10 rows) + +-- The same for hash join. +SET enable_nestloop TO off; +SET enable_hashjoin TO on; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Group Key: p.i + -> Sort + Sort Key: p.i + -> Hash Join + Hash Cond: (p.i = c1.parent) + -> Seq Scan on agg_pushdown_parent p + -> Hash + -> Partial HashAggregate + Group Key: c1.parent + -> Seq Scan on agg_pushdown_child1 c1 +(11 rows) + +-- The same for merge join. +SET enable_hashjoin TO off; +SET enable_mergejoin TO on; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + QUERY PLAN +------------------------------------------------------------ + Finalize GroupAggregate + Group Key: p.i + -> Merge Join + Merge Cond: (p.i = c1.parent) + -> Sort + Sort Key: p.i + -> Seq Scan on agg_pushdown_parent p + -> Sort + Sort Key: c1.parent + -> Partial HashAggregate + Group Key: c1.parent + -> Seq Scan on agg_pushdown_child1 c1 +(12 rows) + +SET enable_nestloop TO on; +SET enable_hashjoin TO on; +-- Scan index on agg_pushdown_child1(parent) column and aggregate the result +-- using AGG_SORTED strategy. +SET enable_seqscan TO off; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.i GROUP BY p.i; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Finalize GroupAggregate + Group Key: p.i + -> Nested Loop + -> Partial GroupAggregate + Group Key: c1.parent + -> Index Scan using agg_pushdown_child1_parent_idx on agg_pushdown_child1 c1 + -> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p + Index Cond: (i = c1.parent) +(8 rows) + +SET enable_seqscan TO on; +-- Join "c1" to "p.x" column, i.e. one that is not in the GROUP BY clause. The +-- planner should still use "c1.parent" as grouping expression for partial +-- aggregation, although it's not in the same equivalence class as the GROUP +-- BY expression ("p.i"). The reason to use "c1.parent" for partial +-- aggregation is that this is the only way for "c1" to provide the join +-- expression with input data. +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v) FROM agg_pushdown_parent AS p JOIN agg_pushdown_child1 +AS c1 ON c1.parent = p.x GROUP BY p.i; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Group Key: p.i + -> Sort + Sort Key: p.i + -> Hash Join + Hash Cond: (p.x = c1.parent) + -> Seq Scan on agg_pushdown_parent p + -> Hash + -> Partial HashAggregate + Group Key: c1.parent + -> Seq Scan on agg_pushdown_child1 c1 +(11 rows) + +-- Perform nestloop join between agg_pushdown_child1 and agg_pushdown_child2 +-- and aggregate the result. +SET enable_nestloop TO on; +SET enable_hashjoin TO off; +SET enable_mergejoin TO off; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN +agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON +c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i; + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Finalize GroupAggregate + Group Key: p.i + -> Sort + Sort Key: p.i + -> Nested Loop + -> Partial HashAggregate + Group Key: c1.parent + -> Nested Loop + -> Seq Scan on agg_pushdown_child1 c1 + -> Index Scan using agg_pushdown_child2_pkey on agg_pushdown_child2 c2 + Index Cond: ((k = c1.j) AND (parent = c1.parent)) + -> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p + Index Cond: (i = c1.parent) +(13 rows) + +-- The same for hash join. +SET enable_nestloop TO off; +SET enable_hashjoin TO on; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN +agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON +c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i; + QUERY PLAN +---------------------------------------------------------------------------------------- + Finalize GroupAggregate + Group Key: p.i + -> Sort + Sort Key: p.i + -> Hash Join + Hash Cond: (p.i = c1.parent) + -> Seq Scan on agg_pushdown_parent p + -> Hash + -> Partial HashAggregate + Group Key: c1.parent + -> Hash Join + Hash Cond: ((c1.parent = c2.parent) AND (c1.j = c2.k)) + -> Seq Scan on agg_pushdown_child1 c1 + -> Hash + -> Seq Scan on agg_pushdown_child2 c2 +(15 rows) + +-- The same for merge join. +SET enable_hashjoin TO off; +SET enable_mergejoin TO on; +SET enable_seqscan TO off; +EXPLAIN (COSTS off) +SELECT p.i, avg(c1.v + c2.v) FROM agg_pushdown_parent AS p JOIN +agg_pushdown_child1 AS c1 ON c1.parent = p.i JOIN agg_pushdown_child2 AS c2 ON +c2.parent = p.i WHERE c1.j = c2.k GROUP BY p.i; + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Finalize GroupAggregate + Group Key: p.i + -> Merge Join + Merge Cond: (c1.parent = p.i) + -> Sort + Sort Key: c1.parent + -> Partial HashAggregate + Group Key: c1.parent + -> Merge Join + Merge Cond: ((c1.j = c2.k) AND (c1.parent = c2.parent)) + -> Index Scan using agg_pushdown_child1_pkey on agg_pushdown_child1 c1 + -> Index Scan using agg_pushdown_child2_pkey on agg_pushdown_child2 c2 + -> Index Only Scan using agg_pushdown_parent_pkey on agg_pushdown_parent p +(13 rows) + diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out index a1c90eb905..55cfd3fff7 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -72,6 +72,7 @@ select count(*) >= 0 as ok from pg_prepared_xacts; select name, setting from pg_settings where name like 'enable%'; name | setting --------------------------------+--------- + enable_agg_pushdown | off enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -89,7 +90,7 @@ select name, setting from pg_settings where name like 'enable%'; enable_seqscan | on enable_sort | on enable_tidscan | on -(17 rows) +(18 rows) -- Test that the pg_timezone_names and pg_timezone_abbrevs views are -- more-or-less working. We can't test their contents in any great detail diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index a39ca1012a..4f0ba643db 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -140,6 +140,7 @@ test: amutils test: stats_ext test: select_parallel test: write_parallel +test: agg_pushdown test: publication test: subscription test: select_views -- 2.16.4 --=-=-=--