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 1p7qUi-0005P4-QE for pgsql-hackers@arkaria.postgresql.org; Wed, 21 Dec 2022 04:14:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p7qUf-0002Ln-Sn for pgsql-hackers@arkaria.postgresql.org; Wed, 21 Dec 2022 04:14:13 +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 1p7qUf-0002H6-97 for pgsql-hackers@lists.postgresql.org; Wed, 21 Dec 2022 04:14:13 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p7qUX-0002ii-5f for pgsql-hackers@lists.postgresql.org; Wed, 21 Dec 2022 04:14:11 +0000 Received: from [192.168.28.53] (cyclops.postgrespro.ru [93.174.131.138]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id B0ADA21C1DA5 for ; Wed, 21 Dec 2022 07:14:02 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1671596042; bh=+tuib+IwEtOwAfxzZiS0O6CVVhX6D/FKJy9c5i11Cvw=; h=Date:From:Subject:To; b=ZhP5Au7hscI1VwQ/hONUcnljNYJYnZW4np7C7SuWc0vDnSQI4jfKNDpXKgGmylYs1 HJhlvzJrPox2DohjewWgznLBcTCaToHl9bvifEYPAR0EYTUagsT+zTfF24cyEegMop Z/4QdxPPaVBsVa50QsQTmMpNaRQDFoqgEojwhLjk= Content-Type: multipart/mixed; boundary="------------6UXWcnweTWNxpDLNRHCyL1hy" Message-ID: <703c09a2-08f3-d2ec-b33d-dbecd62428b8@postgrespro.ru> Date: Wed, 21 Dec 2022 09:14:02 +0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.0 From: Andrey Lepikhov Subject: Optimization issue of branching UNION ALL Content-Language: en-US Organization: Postgres Professional To: PostgreSQL Hackers List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------6UXWcnweTWNxpDLNRHCyL1hy Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi, Client report on a corner case have shown up possible minor non-optimality in procedure of transformation of simple UNION ALL statement tree. Complaint is about auto-generated query with 1E4 simple union all's (see t.sh to generate a demo script). The reason: in REL_11_STABLE it is planned and executed in a second, but REL_12_STABLE and beyond makes matters worse: planning of such a query needs tons of gigabytes of RAM. Superficial study revealed possibly unnecessary operations that could be avoided: 1. Walking across a query by calling substitute_phv_relids() even if lastPHId shows that no one phv is presented. 2. Iterative passes along the append_rel_list for replacing vars in the translated_vars field. I can't grasp real necessity of passing all the append_rel_list during flattening of an union all leaf subquery. No one can reference this leaf, isn't it? In attachment you can see some sketch that reduces a number of planner cycles/copyings. -- Regards Andrey Lepikhov Postgres Professional --------------6UXWcnweTWNxpDLNRHCyL1hy Content-Type: application/x-shellscript; name="t.sh" Content-Disposition: attachment; filename="t.sh" Content-Transfer-Encoding: base64 IyEvYmluL2Jhc2gKCmVjaG8gIkVYUExBSU4iID4gdGVzdC5zcWwKCmVjaG8gIlNFTEVDVCAx LDEiID4+IHRlc3Quc3FsCmZvciBpIGluIHsxLi4xNTAwMH0KZG8KICBlY2hvICIgIFVOSU9O IEFMTCIgPj4gdGVzdC5zcWwKICBlY2hvICJTRUxFQ1QgJGksJGkiID4+IHRlc3Quc3FsCmRv bmUKZWNobyAiOyIgPj4gdGVzdC5zcWwKCg== --------------6UXWcnweTWNxpDLNRHCyL1hy Content-Type: text/x-patch; charset=UTF-8; name="union_all_optimization.patch" Content-Disposition: attachment; filename="union_all_optimization.patch" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wcmVwL3ByZXBqb2ludHJlZS5j IGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3ByZXAvcHJlcGpvaW50cmVlLmMKaW5kZXggMDhh NzNmYjlkODYuLjM3MzllM2ZlN2JhIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6 ZXIvcHJlcC9wcmVwam9pbnRyZWUuYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcHJl cC9wcmVwam9pbnRyZWUuYwpAQCAtMTMxLDcgKzEzMSw3IEBAIHN0YXRpYyBib29sIGZpbmRf ZGVwZW5kZW50X3BodnNfaW5fam9pbnRyZWUoUGxhbm5lckluZm8gKnJvb3QsCiAJCQkJCQkJ CQkJCU5vZGUgKm5vZGUsIGludCB2YXJubyk7CiBzdGF0aWMgdm9pZCBzdWJzdGl0dXRlX3Bo dl9yZWxpZHMoTm9kZSAqbm9kZSwKIAkJCQkJCQkJICBpbnQgdmFybm8sIFJlbGlkcyBzdWJy ZWxpZHMpOwotc3RhdGljIHZvaWQgZml4X2FwcGVuZF9yZWxfcmVsaWRzKExpc3QgKmFwcGVu ZF9yZWxfbGlzdCwgaW50IHZhcm5vLAorc3RhdGljIHZvaWQgZml4X2FwcGVuZF9yZWxfcmVs aWRzKFBsYW5uZXJJbmZvICpyb290LCBpbnQgdmFybm8sCiAJCQkJCQkJCSAgUmVsaWRzIHN1 YnJlbGlkcyk7CiBzdGF0aWMgTm9kZSAqZmluZF9qb2ludHJlZV9ub2RlX2Zvcl9yZWwoTm9k ZSAqanRub2RlLCBpbnQgcmVsaWQpOwogCkBAIC0xMTU2LDggKzExNTYsOSBAQCBwdWxsX3Vw X3NpbXBsZV9zdWJxdWVyeShQbGFubmVySW5mbyAqcm9vdCwgTm9kZSAqanRub2RlLCBSYW5n ZVRibEVudHJ5ICpydGUsCiAJCVJlbGlkcwkJc3VicmVsaWRzOwogCiAJCXN1YnJlbGlkcyA9 IGdldF9yZWxpZHNfaW5fam9pbnRyZWUoKE5vZGUgKikgc3VicXVlcnktPmpvaW50cmVlLCBm YWxzZSk7Ci0JCXN1YnN0aXR1dGVfcGh2X3JlbGlkcygoTm9kZSAqKSBwYXJzZSwgdmFybm8s IHN1YnJlbGlkcyk7Ci0JCWZpeF9hcHBlbmRfcmVsX3JlbGlkcyhyb290LT5hcHBlbmRfcmVs X2xpc3QsIHZhcm5vLCBzdWJyZWxpZHMpOworCQlpZiAocm9vdC0+Z2xvYi0+bGFzdFBISWQg IT0gMCkKKwkJCXN1YnN0aXR1dGVfcGh2X3JlbGlkcygoTm9kZSAqKSBwYXJzZSwgdmFybm8s IHN1YnJlbGlkcyk7CisJCWZpeF9hcHBlbmRfcmVsX3JlbGlkcyhyb290LCB2YXJubywgc3Vi cmVsaWRzKTsKIAl9CiAKIAkvKgpAQCAtMjA2NCwxNyArMjA2NSwyNSBAQCBwZXJmb3JtX3B1 bGx1cF9yZXBsYWNlX3ZhcnMoUGxhbm5lckluZm8gKnJvb3QsCiAJICogdXNlIFBIVnMgZm9y IHNhZmV0eS4gIChUaGlzIGFuYWx5c2lzIGNvdWxkIGJlIG1hZGUgdGlnaHRlciBidXQgaXQg c2VlbXMKIAkgKiB1bmxpa2VseSB0byBiZSB3b3J0aCBtdWNoIHRyb3VibGUuKQogCSAqLwot CWZvcmVhY2gobGMsIHJvb3QtPmFwcGVuZF9yZWxfbGlzdCkKKwlpZiAoY29udGFpbmluZ19h cHBlbmRyZWwpCiAJewotCQlBcHBlbmRSZWxJbmZvICphcHBpbmZvID0gKEFwcGVuZFJlbElu Zm8gKikgbGZpcnN0KGxjKTsKLQkJYm9vbAkJc2F2ZV9uZWVkX3BodnMgPSBydmNvbnRleHQt Pm5lZWRfcGh2czsKKwkJYm9vbCBzYXZlX25lZWRfcGh2cyA9IHJ2Y29udGV4dC0+bmVlZF9w aHZzOwogCi0JCWlmIChhcHBpbmZvID09IGNvbnRhaW5pbmdfYXBwZW5kcmVsKQotCQkJcnZj b250ZXh0LT5uZWVkX3BodnMgPSBmYWxzZTsKLQkJYXBwaW5mby0+dHJhbnNsYXRlZF92YXJz ID0gKExpc3QgKikKLQkJCXB1bGx1cF9yZXBsYWNlX3ZhcnMoKE5vZGUgKikgYXBwaW5mby0+ dHJhbnNsYXRlZF92YXJzLCBydmNvbnRleHQpOworCQlydmNvbnRleHQtPm5lZWRfcGh2cyA9 IGZhbHNlOworCQljb250YWluaW5nX2FwcGVuZHJlbC0+dHJhbnNsYXRlZF92YXJzID0gKExp c3QgKikKKwkJCXB1bGx1cF9yZXBsYWNlX3ZhcnMoKE5vZGUgKikgY29udGFpbmluZ19hcHBl bmRyZWwtPnRyYW5zbGF0ZWRfdmFycywKKwkJCQkJCQkJcnZjb250ZXh0KTsKIAkJcnZjb250 ZXh0LT5uZWVkX3BodnMgPSBzYXZlX25lZWRfcGh2czsKIAl9CisJZWxzZQorCXsKKwkJZm9y ZWFjaChsYywgcm9vdC0+YXBwZW5kX3JlbF9saXN0KQorCQl7CisJCQlBcHBlbmRSZWxJbmZv ICphcHBpbmZvID0gKEFwcGVuZFJlbEluZm8gKikgbGZpcnN0KGxjKTsKKwkJCWFwcGluZm8t PnRyYW5zbGF0ZWRfdmFycyA9IChMaXN0ICopCisJCQkJcHVsbHVwX3JlcGxhY2VfdmFycygo Tm9kZSAqKSBhcHBpbmZvLT50cmFuc2xhdGVkX3ZhcnMsIHJ2Y29udGV4dCk7CisJCX0KKwl9 CiAKIAkvKgogCSAqIFJlcGxhY2UgcmVmZXJlbmNlcyBpbiB0aGUgam9pbmFsaWFzdmFycyBs aXN0cyBvZiBqb2luIFJURXMuCkBAIC0zMjczLDcgKzMyODIsNyBAQCByZW1vdmVfcmVzdWx0 X3JlZnMoUGxhbm5lckluZm8gKnJvb3QsIGludCB2YXJubywgTm9kZSAqbmV3anRsb2MpCiAJ CXN1YnJlbGlkcyA9IGdldF9yZWxpZHNfaW5fam9pbnRyZWUobmV3anRsb2MsIGZhbHNlKTsK IAkJQXNzZXJ0KCFibXNfaXNfZW1wdHkoc3VicmVsaWRzKSk7CiAJCXN1YnN0aXR1dGVfcGh2 X3JlbGlkcygoTm9kZSAqKSByb290LT5wYXJzZSwgdmFybm8sIHN1YnJlbGlkcyk7Ci0JCWZp eF9hcHBlbmRfcmVsX3JlbGlkcyhyb290LT5hcHBlbmRfcmVsX2xpc3QsIHZhcm5vLCBzdWJy ZWxpZHMpOworCQlmaXhfYXBwZW5kX3JlbF9yZWxpZHMocm9vdCwgdmFybm8sIHN1YnJlbGlk cyk7CiAJfQogCiAJLyoKQEAgLTM0OTIsNyArMzUwMSw3IEBAIHN1YnN0aXR1dGVfcGh2X3Jl bGlkcyhOb2RlICpub2RlLCBpbnQgdmFybm8sIFJlbGlkcyBzdWJyZWxpZHMpCiAgKiBXZSBh c3N1bWUgd2UgbWF5IG1vZGlmeSB0aGUgQXBwZW5kUmVsSW5mbyBub2RlcyBpbi1wbGFjZS4K ICAqLwogc3RhdGljIHZvaWQKLWZpeF9hcHBlbmRfcmVsX3JlbGlkcyhMaXN0ICphcHBlbmRf cmVsX2xpc3QsIGludCB2YXJubywgUmVsaWRzIHN1YnJlbGlkcykKK2ZpeF9hcHBlbmRfcmVs X3JlbGlkcyhQbGFubmVySW5mbyAqcm9vdCwgaW50IHZhcm5vLCBSZWxpZHMgc3VicmVsaWRz KQogewogCUxpc3RDZWxsICAgKmw7CiAJaW50CQkJc3VidmFybm8gPSAtMTsKQEAgLTM1MDMs NyArMzUxMiw3IEBAIGZpeF9hcHBlbmRfcmVsX3JlbGlkcyhMaXN0ICphcHBlbmRfcmVsX2xp c3QsIGludCB2YXJubywgUmVsaWRzIHN1YnJlbGlkcykKIAkgKiBBcHBlbmRSZWxJbmZvIG5v ZGVzIHJlZmVyIHRvIGl0LiAgU28gY29tcHV0ZSBpdCBvbiBmaXJzdCB1c2UuIE5vdGUgdGhh dAogCSAqIGJtc19zaW5nbGV0b25fbWVtYmVyIHdpbGwgY29tcGxhaW4gaWYgc2V0IGlzIG5v dCBzaW5nbGV0b24uCiAJICovCi0JZm9yZWFjaChsLCBhcHBlbmRfcmVsX2xpc3QpCisJZm9y ZWFjaChsLCByb290LT5hcHBlbmRfcmVsX2xpc3QpCiAJewogCQlBcHBlbmRSZWxJbmZvICph cHBpbmZvID0gKEFwcGVuZFJlbEluZm8gKikgbGZpcnN0KGwpOwogCkBAIC0zNTE4LDggKzM1 MjcsOSBAQCBmaXhfYXBwZW5kX3JlbF9yZWxpZHMoTGlzdCAqYXBwZW5kX3JlbF9saXN0LCBp bnQgdmFybm8sIFJlbGlkcyBzdWJyZWxpZHMpCiAJCX0KIAogCQkvKiBBbHNvIGZpeCB1cCBh bnkgUEhWcyBpbiBpdHMgdHJhbnNsYXRlZCB2YXJzICovCi0JCXN1YnN0aXR1dGVfcGh2X3Jl bGlkcygoTm9kZSAqKSBhcHBpbmZvLT50cmFuc2xhdGVkX3ZhcnMsCi0JCQkJCQkJICB2YXJu bywgc3VicmVsaWRzKTsKKwkJaWYgKHJvb3QtPmdsb2ItPmxhc3RQSElkICE9IDApCisJCQlz dWJzdGl0dXRlX3Bodl9yZWxpZHMoKE5vZGUgKikgYXBwaW5mby0+dHJhbnNsYXRlZF92YXJz LAorCQkJCQkJCQkgIHZhcm5vLCBzdWJyZWxpZHMpOwogCX0KIH0KIAo= --------------6UXWcnweTWNxpDLNRHCyL1hy--