Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLi6M-0001ub-JD for pgsql-bugs@arkaria.postgresql.org; Fri, 25 Sep 2020 07:25:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kLi6L-0003dn-HQ for pgsql-bugs@arkaria.postgresql.org; Fri, 25 Sep 2020 07:25:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLead-0008C1-VW for pgsql-bugs@lists.postgresql.org; Fri, 25 Sep 2020 03:40:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLeaa-00055V-TR for pgsql-bugs@lists.postgresql.org; Fri, 25 Sep 2020 03:40:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: MIME-Version:Content-Type:Sender:Content-Transfer-Encoding:Content-ID: Content-Description:In-Reply-To:References; bh=gdQG7H1PQ0DEAPabusksfXJpGPOz6dd6VJVd1DVzdds=; b=Hpv4gl6dcZDZxM7Fkee9iFXMn7 dNBkiSugqPSk+6FtndViBqFPMJ73zhw7WhFCesa3mDnwikuBYdl6j7ZyYbXupwUDP552ivVBCO6kV ZqUbOjgXEbnQFYI9FcUBN0ddHO0fZ252kMaK9e/jlYJx2BIOLmt+5/Ex7NJEfMZL88rMZdyuBzUXz SqnUmzDjlkJUC73Ko6crBJz7l2N+KOSY7Rp+zOdw8es5Bpw2O6f5vfaNh0xjPkySSK4ohXYs6kfVx JCvjXQkpX4WtrRvYKkrHTWn28pfYh41/u5wVdmbF6UsssxwaiDjBQRLLlv1MqEAKSDAXYNH7Zc1CX sNgTHgbg==; Received: from wrigleys.postgresql.org ([2a02:c0:301:0:ffff::34]) by mahout.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLeaa-0008O0-64 for pgsql-bugs@lists.postgresql.org; Fri, 25 Sep 2020 03:40:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kLeaY-0002iy-OC for pgsql-bugs@lists.postgresql.org; Fri, 25 Sep 2020 03:40:02 +0000 Content-Type: multipart/mixed; boundary="===============9182163079533857421==" MIME-Version: 1.0 Subject: BUG #16635: Query Optimizer - Performance optimization for the UNION function To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: XINYULIU@UMICH.EDU Reply-To: XINYULIU@UMICH.EDU, pgsql-bugs@lists.postgresql.org Date: Fri, 25 Sep 2020 03:39:16 +0000 Message-ID: <16635-b06e1ff6be5a1975@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --===============9182163079533857421== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNjYzNQpMb2dnZWQgYnk6ICAgICAgICAgIFhJTllVIExJVQpFbWFpbCBh ZGRyZXNzOiAgICAgIFhJTllVTElVQFVNSUNILkVEVQpQb3N0Z3JlU1FMIHZlcnNpb246IDEzLjAK T3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgMjAuMDQKRGVzY3JpcHRpb246ICAgICAgICAKCkhl bGxvLA0KDQpXZSBhcmUgc2hhcmluZyBhIHBhaXIgb2YgVFBDLUggcXVlcmllcyB0aGF0IHNob3cg YSBwb3RlbnRpYWwgcGVyZm9ybWFuY2UKb3B0aW1pemF0aW9uIGluIHRoaXMgcmVwb3J0Og0KDQpG aXJzdCBxdWVyeToNClNFTEVDVCAib19vcmRlcnN0YXR1cyIsIA0KICAgICAgICJvX2NsZXJrIiwg DQogICAgICAgIm9fY29tbWVudCIgDQpGUk9NICAgIm9yZGVycyIgDQpXSEVSRSAgIm9fb3JkZXJz dGF0dXMiIExJS0UgJyUnIA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgIHx8ICdGJyANClVO SU9OIA0KU0VMRUNUICJvX29yZGVyc3RhdHVzIiwgDQogICAgICAgIm9fY2xlcmsiLCANCiAgICAg ICAib19jb21tZW50IiANCkZST00gICAib3JkZXJzIjsgICAgDQoNClNlY29uZCBxdWVyeToNClNF TEVDVCAib19vcmRlcnN0YXR1cyIsIA0KICAgICAgICJvX2NsZXJrIiwgDQogICAgICAgIm9fY29t bWVudCIgDQpGUk9NICAgKFNFTEVDVCAib19vcmRlcnN0YXR1cyIsIA0KICAgICAgICAgICAgICAg Im9fY2xlcmsiLCANCiAgICAgICAgICAgICAgICJvX2NvbW1lbnQiIA0KICAgICAgICBGUk9NICAg Im9yZGVycyIgDQogICAgICAgIFdIRVJFICAib19vcmRlcnN0YXR1cyIgTElLRSAnJScgDQogICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8fCAnRicgDQogICAgICAgIFVOSU9OIEFM TCANCiAgICAgICAgU0VMRUNUICJvX29yZGVyc3RhdHVzIiwgDQogICAgICAgICAgICAgICAib19j bGVyayIsIA0KICAgICAgICAgICAgICAgIm9fY29tbWVudCIgDQogICAgICAgIEZST00gICAib3Jk ZXJzIikgQVMgInQ2IiANCkdST1VQICBCWSAib19vcmRlcnN0YXR1cyIsIA0KICAgICAgICAgICJv X2NsZXJrIiwgDQogICAgICAgICAgIm9fY29tbWVudCI7IA0KDQpbQWN0dWFsIEJlaGF2aW9yXQ0K V2UgZXhlY3V0ZWQgYm90aCBxdWVyaWVzIG9uIHRoZSBUUEMtSCBiZW5jaG1hcmsgb2Ygc2NhbGUg ZmFjdG9yIDU6IHRoZSBmaXJzdApxdWVyeSB0YWtlcyA0MCBzZWNvbmRzLCB3aGlsZSB0aGUgc2Vj b25kIHF1ZXJ5IG9ubHkgdGFrZXMgMTUgc2Vjb25kcy4gV2UKdGhpbmsgdGhlIHRpbWUgZGlmZmVy ZW5jZSByZXN1bHRzIGZyb20gZGlmZmVyZW50IHBsYW5zIHNlbGVjdGVkLg0KDQpbUXVlcnkgRXhl Y3V0aW9uIFBsYW5dDQpGaXJzdCBxdWVyeToNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAKICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIA0K LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBVbmlxdWUgIChjb3N0PTU4MDc2NDUuNjku LjU5MTk1MjEuNzEgcm93cz0xMTE4NzYwMiB3aWR0aD0yNDgpIChhY3R1YWwKdGltZT0zMjcxMy4w MjkuLjQwMjY1LjM3OCByb3dzPTc0OTk5MzIgbG9vcHM9MSkNCiAgIC0+ICBTb3J0ICAoY29zdD01 ODA3NjQ1LjY5Li41ODM1NjE0LjY5IHJvd3M9MTExODc2MDIgd2lkdGg9MjQ4KSAoYWN0dWFsCnRp bWU9MzI3MTMuMDI4Li4zODc2Ni43MTggcm93cz0xMTE1NDUwMSBsb29wcz0xKQ0KICAgICAgICAg U29ydCBLZXk6IG9yZGVycy5vX29yZGVyc3RhdHVzLCBvcmRlcnMub19jbGVyaywgb3JkZXJzLm9f Y29tbWVudA0KICAgICAgICAgU29ydCBNZXRob2Q6IGV4dGVybmFsIG1lcmdlICBEaXNrOiA4NTQw ODBrQg0KICAgICAgICAgLT4gIEFwcGVuZCAgKGNvc3Q9MC4wMC4uNTk3NDYyLjk1IHJvd3M9MTEx ODc2MDIgd2lkdGg9MjQ4KSAoYWN0dWFsCnRpbWU9MC4zODAuLjI5OTUuOTAxIHJvd3M9MTExNTQ1 MDEgbG9vcHM9MSkNCiAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBvcmRlcnMgIChjb3N0 PTAuMDAuLjIyNDE5OC4yOSByb3dzPTM2ODg1MzkKd2lkdGg9NjcpIChhY3R1YWwgdGltZT0wLjM3 OS4uMTQyMC43NTggcm93cz0zNjU0NTAxIGxvb3BzPTEpDQogICAgICAgICAgICAgICAgICAgICBG aWx0ZXI6IChvX29yZGVyc3RhdHVzIH5+ICclRic6OnRleHQpDQogICAgICAgICAgICAgICAgICAg ICBSb3dzIFJlbW92ZWQgYnkgRmlsdGVyOiAzODQ1NDk5DQogICAgICAgICAgICAgICAtPiAgU2Vx IFNjYW4gb24gb3JkZXJzIG9yZGVyc18xICAoY29zdD0wLjAwLi4yMDU0NTAuNjMKcm93cz03NDk5 MDYzIHdpZHRoPTY3KSAoYWN0dWFsIHRpbWU9MC4wMTYuLjk4NC4zNDcgcm93cz03NTAwMDAwIGxv b3BzPTEpDQogUGxhbm5pbmcgVGltZTogNS41NjAgbXMNCiBFeGVjdXRpb24gVGltZTogNDA1MTcu ODY1IG1zDQooMTEgcm93cykNCg0KDQoNCg0KDQoNClNlY29uZCBxdWVyeToNCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg IFFVRVJZClBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAKIA0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0NCiBGaW5hbGl6ZSBIYXNoQWdncmVnYXRlICAoY29zdD0xMjEwMDg2 LjE3Li4xMjQ3NDk0LjcwIHJvd3M9MTExODc2MCB3aWR0aD02NykKKGFjdHVhbCB0aW1lPTEwNDg3 LjgyOS4uMTQ3ODAuMTY1IHJvd3M9NzQ5OTkzMiBsb29wcz0xKQ0KICAgR3JvdXAgS2V5OiBvcmRl cnNfMS5vX29yZGVyc3RhdHVzLCBvcmRlcnNfMS5vX2NsZXJrLCBvcmRlcnNfMS5vX2NvbW1lbnQN CiAgIFBsYW5uZWQgUGFydGl0aW9uczogNjQgIEJhdGNoZXM6IDY5NyAgTWVtb3J5IFVzYWdlOiA0 NjI1a0IgIERpc2sgVXNhZ2U6Cjk4NDMwNGtCDQogICAtPiAgR2F0aGVyICAoY29zdD03NzM2Mzku ODMuLjEwODg0MjEuMDIgcm93cz0yMjM3NTIwIHdpZHRoPTY3KSAoYWN0dWFsCnRpbWU9MzkzNy4y NTMuLjY5MTcuOTcyIHJvd3M9MTExNTM2NjYgbG9vcHM9MSkNCiAgICAgICAgIFdvcmtlcnMgUGxh bm5lZDogMg0KICAgICAgICAgV29ya2VycyBMYXVuY2hlZDogMg0KICAgICAgICAgLT4gIFBhcnRp YWwgSGFzaEFnZ3JlZ2F0ZSAgKGNvc3Q9NzcyNjM5LjgzLi44NjM2NjkuMDIgcm93cz0xMTE4NzYw CndpZHRoPTY3KSAoYWN0dWFsIHRpbWU9MzkzMi45OTcuLjU2MzYuODA4IHJvd3M9MzcxNzg4OSBs b29wcz0zKQ0KICAgICAgICAgICAgICAgR3JvdXAgS2V5OiBvcmRlcnNfMS5vX29yZGVyc3RhdHVz LCBvcmRlcnNfMS5vX2NsZXJrLApvcmRlcnNfMS5vX2NvbW1lbnQNCiAgICAgICAgICAgICAgIFBs YW5uZWQgUGFydGl0aW9uczogNjQgIEJhdGNoZXM6IDQyNSAgTWVtb3J5IFVzYWdlOiA0MTg1a0Ig CkRpc2sgVXNhZ2U6IDMyMzU4NGtCDQogICAgICAgICAgICAgICBXb3JrZXIgMDogIEJhdGNoZXM6 IDM5MyAgTWVtb3J5IFVzYWdlOiA0MTg1a0IgIERpc2sgVXNhZ2U6CjMyMzU4NGtCDQogICAgICAg ICAgICAgICBXb3JrZXIgMTogIEJhdGNoZXM6IDQ1MyAgTWVtb3J5IFVzYWdlOiA0MTg1a0IgIERp c2sgVXNhZ2U6CjM3MDY4OGtCDQogICAgICAgICAgICAgICAtPiAgUGFyYWxsZWwgQXBwZW5kICAo Y29zdD0wLjAwLi40MDIxNzQuODUgcm93cz02ODEzMTQ5CndpZHRoPTY3KSAoYWN0dWFsIHRpbWU9 MC4xMzkuLjI0NzUuNzYxIHJvd3M9MzcxODE2NyBsb29wcz0zKQ0KICAgICAgICAgICAgICAgICAg ICAgLT4gIFBhcmFsbGVsIFNlcSBTY2FuIG9uIG9yZGVycyBvcmRlcnNfMSAKKGNvc3Q9MC4wMC4u MTY5NTE3LjYyIHJvd3M9MTUzNjg5MSB3aWR0aD02NykgKGFjdHVhbCB0aW1lPTAuMDE0Li43Njku NzI1CnJvd3M9MTIxODE2NyBsb29wcz0zKQ0KICAgICAgICAgICAgICAgICAgICAgICAgICAgRmls dGVyOiAob19vcmRlcnN0YXR1cyB+fiAnJUYnOjp0ZXh0KQ0KICAgICAgICAgICAgICAgICAgICAg ICAgICAgUm93cyBSZW1vdmVkIGJ5IEZpbHRlcjogMTI4MTgzMw0KICAgICAgICAgICAgICAgICAg ICAgLT4gIFBhcmFsbGVsIFNlcSBTY2FuIG9uIG9yZGVycyAgKGNvc3Q9MC4wMC4uMTYxNzA2LjEw CnJvd3M9MzEyNDYxMCB3aWR0aD02NykgKGFjdHVhbCB0aW1lPTAuMjAyLi4yMTkzLjAzMyByb3dz PTM3NTAwMDAgbG9vcHM9MikNCiBQbGFubmluZyBUaW1lOiA1LjAyNSBtcw0KIEV4ZWN1dGlvbiBU aW1lOiAxNTA2Ny44MDkgbXMNCigxOCByb3dzKQ0KDQoNCg0KDQoNCg0KW0V4cGVjdGVkIEJlaGF2 aW9yXQ0KU2luY2UgdGhlc2UgdHdvIHF1ZXJpZXMgYXJlIHNlbWFudGljYWxseSBlcXVpdmFsZW50 LCB3ZSB3YW50IHRvIGtub3cgd2hldGhlcgpQb3N0Z3JlU1FMIHdvdWxkIGNvbnNpZGVyIGFkZGlu ZyBzdWNoIG9wdGltaXphdGlvbiBnaXZlbiB0aGUgcG90ZW50aWFsIG9mCnBlcmZvcm1hbmNlIGlt cHJvdmVtZW50Lg0KDQpbVGVzdCBFbnZpcm9ubWVudF0NClVidW50dSAyMC4wNCBtYWNoaW5lICJM aW51eCBwYW5kYSA1LjQuMC00MC1nZW5lcmljICM0NC1VYnVudHUgU01QIFR1ZSBKdW4gMjMKMDA6 MDE6MDQgVVRDIDIwMjAgeDg2XzY0IHg4Nl82NCB4ODZfNjQgR05VL0xpbnV4Ig0KUG9zdGdyZVNR TCB2MTIuMw0KRGF0YWJhc2U6IFRQQy1IIGJlbmNobWFyayAod2l0aCBzY2FsZSBmYWN0b3IgNSkN Ck91dHB1dCBvZiB0aGUg4oCcXGQgb3JkZXJz4oCdOiANCg0KdHBjaDU9IyBcZCBvcmRlcnMNCiAg ICAgICAgICAgICAgICAgICAgICAgICAgVGFibGUgInB1YmxpYy5vcmRlcnMiDQogICAgIENvbHVt biAgICAgIHwgICAgICAgICBUeXBlICAgICAgICAgIHwgQ29sbGF0aW9uIHwgTnVsbGFibGUgfCBE ZWZhdWx0IA0KLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0t LS0tLS0rLS0tLS0tLS0tLSstLS0tLS0tLS0NCiBvX29yZGVya2V5ICAgICAgfCBpbnRlZ2VyICAg ICAgICAgICAgICAgfCAgICAgICAgICAgfCBub3QgbnVsbCB8IA0KIG9fY3VzdGtleSAgICAgICB8 IGludGVnZXIgICAgICAgICAgICAgICB8ICAgICAgICAgICB8IG5vdCBudWxsIHwgDQogb19vcmRl cnN0YXR1cyAgIHwgY2hhcmFjdGVyKDEpICAgICAgICAgIHwgICAgICAgICAgIHwgbm90IG51bGwg fCANCiBvX3RvdGFscHJpY2UgICAgfCBudW1lcmljKDE1LDIpICAgICAgICAgfCAgICAgICAgICAg fCBub3QgbnVsbCB8IA0KIG9fb3JkZXJkYXRlICAgICB8IGRhdGUgICAgICAgICAgICAgICAgICB8 ICAgICAgICAgICB8IG5vdCBudWxsIHwgDQogb19vcmRlcnByaW9yaXR5IHwgY2hhcmFjdGVyKDE1 KSAgICAgICAgIHwgICAgICAgICAgIHwgbm90IG51bGwgfCANCiBvX2NsZXJrICAgICAgICAgfCBj aGFyYWN0ZXIoMTUpICAgICAgICAgfCAgICAgICAgICAgfCBub3QgbnVsbCB8IA0KIG9fc2hpcHBy aW9yaXR5ICB8IGludGVnZXIgICAgICAgICAgICAgICB8ICAgICAgICAgICB8IG5vdCBudWxsIHwg DQogb19jb21tZW50ICAgICAgIHwgY2hhcmFjdGVyIHZhcnlpbmcoNzkpIHwgICAgICAgICAgIHwg bm90IG51bGwgfCANCkluZGV4ZXM6DQogICAgIm9yZGVyc19wa2V5IiBQUklNQVJZIEtFWSwgYnRy ZWUgKG9fb3JkZXJrZXkpDQpGb3JlaWduLWtleSBjb25zdHJhaW50czoNCiAgICAib3JkZXJzX2Zr MSIgRk9SRUlHTiBLRVkgKG9fY3VzdGtleSkgUkVGRVJFTkNFUyBjdXN0b21lcihjX2N1c3RrZXkp DQpSZWZlcmVuY2VkIGJ5Og0KICAgIFRBQkxFICJsaW5laXRlbSIgQ09OU1RSQUlOVCAibGluZWl0 ZW1fZmsxIiBGT1JFSUdOIEtFWSAobF9vcmRlcmtleSkKUkVGRVJFTkNFUyBvcmRlcnMob19vcmRl cmtleSkNCg0KDQoNCg0KDQoNCltTdGVwcyBmb3IgcmVwcm9kdWNpbmcgb3VyIG9ic2VydmF0aW9u czpdCQ0KRG93bmxvYWQgdGhlIGRhdGFzZXQgZnJvbSB0aGUgbGluazoKaHR0cHM6Ly9kcml2ZS5n b29nbGUuY29tL2ZpbGUvZC8xM3JGYTFCTkRpNGUyUm1YQm4teUVRa2NxdDZsc0J1MWMvdmlldz91 c3A9c2hhcmluZw0KU2V0IHVwIFRQQy1IIGJlbmNobWFyaw0KdGFyIHh6dmYgdHBjaDVfcG9zdGdy ZXNxbC50YXIuZ3oNCmNkIHRwY2g1X3Bvc3RncmVzcWwNCmRiPXRwY2g1DQpjcmVhdGVkYiAkZGIN CnBzcWwgLWQgJGRiIDwgZHNzLmRkbA0KZm9yIGkgaW4gYGxzICoudGJsYA0KZG8NCiAgICBlY2hv ICRpDQogICAgbmFtZT1gZWNobyAkaXxjdXQgLWQnLicgLWYxYA0KICAgIHBzcWwgLWQgJGRiIC1j ICJDT1BZICRuYW1lIEZST00gJ2Bwd2RgLyRpJyBERUxJTUlURVIgJ3wnIEVOQ09ESU5HCidMQVRJ TjEnOyINCmRvbmUNCnBzcWwgLWQgJGRiIDwgZHNzX3Bvc3RncmVzLnJpDQpFeGVjdXRlIHRoZSBx dWVyaWVzCgo= --===============9182163079533857421==--