Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tza9S-003rQA-EP for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Apr 2025 11:51:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tza9P-001BkD-2I for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Apr 2025 11:51:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tza9O-001Bk5-NS for pgsql-hackers@lists.postgresql.org; Tue, 01 Apr 2025 11:51:26 +0000 Received: from mail-wr1-x434.google.com ([2a00:1450:4864:20::434]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tza9M-002kFD-0Z for pgsql-hackers@lists.postgresql.org; Tue, 01 Apr 2025 11:51:26 +0000 Received: by mail-wr1-x434.google.com with SMTP id ffacd0b85a97d-39c0dfba946so2198850f8f.3 for ; Tue, 01 Apr 2025 04:51:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1743508282; x=1744113082; darn=lists.postgresql.org; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=+FLwo2czf0OHJCHuwm6WFhV+Y5H5u6LSQhtGvx+WPxE=; b=brVQ91Zji4CeRDmS5YQzOluWVFUbVYtxA3mVEnSpM6QfTsygYj3CS2C8AYF+3QPu5r QiCNCs1oawmJqft5SmFxt3gNSzgC2O7etpKX2QR55QV5++fM+oGEdc2zWYvXt8ld4kGw z5Ae5wt1nJn3PkdxiDV5AHrYWROzhjZcSdZvJUAy4mPl3p59QMJogHPazmYJZrDp+KPf j4UQ60QPhwxoIoaGcBtG2BF61S+QxPhWBaSnpyVseUwK1RkwPXs/6EswP5/BZfkpobsR UF3M1uNhqAmp17pdsg1w8UjokDy7/8eLsWxkpt6YmKR2pJ2pcpefrUg7NKpcrqGhecQ2 xDeQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743508282; x=1744113082; h=subject:from:to:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=+FLwo2czf0OHJCHuwm6WFhV+Y5H5u6LSQhtGvx+WPxE=; b=iOR3tJWIHnJbX6cq6hgq9CNPGJM1oTWhtBA9XsMruqcQhM1ums895pp3/E9hne2wE5 59X1O2xPDheyRbam28//KpCRJxcwufv4RRQ68VZpoPxBjt+ODKCK14Il6BnNWU+b4abN EHQm5RdsAJZ0q1d0Htxi5772hdiixiAooUikDAdLnkyx4U4iu6DVvNjI1M0iGbGb6SCL ZHYgdIKZjOcIEs/cQWyHYm9Qcqx7fXodxbCRbb8aSjrtLaR5C2XMYRcZgelU6fecp2eY k37HYjLWrEY5+gscHwV4EikXv4SxQHAJccD2Wjx/3O3HYUIqP8GYSYPVrOBAjB1qUHBt PGTA== X-Gm-Message-State: AOJu0YwRtAiJFxkQLap1f4bftWcei9ljFyErWJb/5vVElox2q2C4AWIg Mk2GXCsqjO3ACt3xuws12BNt5RIwbvNaUc2Uto7Jsdz2wfbQ+phP7d6q7Q== X-Gm-Gg: ASbGncs8JpiwKyvbR59rcSz3NUprFOKQQA2tbQU4UKoQ8EHlOdGCacn/3L1sZ4I34Y0 TprtlQloDfv96yXxbEaBkPZXQkpb9SG0PWvf6Q+JwOD/VPRhEGlXAYUSpByDfU+AxyQECfZkhcy OMF02M8PTVIv42n0NiMX2QPC2H04cMGkOCBRP+0AbiwLfUDCEXI1lfvS8nqfPh7N8Zzcqy6aMhA Pd/VQKzSgP/pYBTHuFkHAOUUwcMM/6kTMOQszBKcvKSlMdO2iErqynYbXUYOJJNHIqcuzptx8xo WiB8Ck/JiHNSyeHkV23NqDg/DnhaQhV2hFpijBl2vLrvwWpTJOpdScZrjs+5eiVB9H7FUgU+MeF Gr/+cbyklEe/VbxKzI+eIzl5aQ2MStcPEwgqII4vOog== X-Google-Smtp-Source: AGHT+IHNWjmB34SCnASYmuTko2chRuRxy+a+EGUJHHaNM0BhdcSJW4DR6NmvCfmvvFy4xRKgCviZbA== X-Received: by 2002:a05:6000:4203:b0:391:31f2:b998 with SMTP id ffacd0b85a97d-39c120cb8a3mr9746251f8f.6.1743508281953; Tue, 01 Apr 2025 04:51:21 -0700 (PDT) Received: from [10.255.186.197] (vip-148-139-2-229.cust.service-now.com. [148.139.2.229]) by smtp.gmail.com with ESMTPSA id 5b1f17b1804b1-43d8ff02e84sm153253335e9.32.2025.04.01.04.51.20 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 01 Apr 2025 04:51:21 -0700 (PDT) Content-Type: multipart/mixed; boundary="------------o5jhd1mCyohvPhca0D0ygUIS" Message-ID: <29a76f51-97b0-4c07-87b7-ec8e3b5345c9@gmail.com> Date: Tue, 1 Apr 2025 13:51:18 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US To: pgsql-hackers@lists.postgresql.org From: David Geier Subject: Hashed IN only applied to first encountered IN 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. --------------o5jhd1mCyohvPhca0D0ygUIS Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi hackers, The hashed IN optimization is only applied to the first encountered ScalarArrayOpExpr during the expression tree traversal in convert_saop_to_hashed_saop_walker(). Reason being that the walker returns true which aborts the traversal. This can be exhibited by running a query with two IN statements vs the same query with just a single IN statement. The IN statements are combined via OR and both statements return no rows to prevent any kind of lazy evaluation optimizations. The query with two IN statements is 6x slower than the the query with the single IN statement. See the attached example. I've also attached a patch with a fix. -- David Geier (ServiceNow) --------------o5jhd1mCyohvPhca0D0ygUIS Content-Type: text/plain; charset=UTF-8; name="example.sql" Content-Disposition: attachment; filename="example.sql" Content-Transfer-Encoding: base64 Q1JFQVRFIFRBQkxFIHRlc3QgKGNvbCBURVhUKTsKSU5TRVJUIElOVE8gdGVzdCBzZWxlY3Qg J3Rlc3R0ZXN0dGVzdCcgRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDAwMDApOwpcdGlt aW5nIG9uCgpTRUxFQ1QgKiBGUk9NCiAgdGVzdApXSEVSRQogIGNvbCBJTiAoJ3Rlc3R0ZXN0 dGVzdHRlc2EnLCAndGVzdHRlc3R0ZXN0dGVzYicsICd0ZXN0dGVzdHRlc3R0ZXNjJywgJ3Rl c3R0ZXN0dGVzdHRlc2QnLCAndGVzdHRlc3R0ZXN0dGVzZScsICd0ZXN0dGVzdHRlc3R0ZXNm JywgJ3Rlc3R0ZXN0dGVzdHRlc2cnLCAndGVzdHRlc3R0ZXN0dGVzaCcsICd0ZXN0dGVzdHRl c3R0ZXNpJywgJ3Rlc3R0ZXN0dGVzdHRlc2onLCAndGVzdHRlc3R0ZXN0dGVzaycsICd0ZXN0 dGVzdHRlc3R0ZXNsJywgJ3Rlc3R0ZXN0dGVzdHRlc20nLCAndGVzdHRlc3R0ZXN0dGVzbics ICd0ZXN0dGVzdHRlc3R0ZXNvJywgJ3Rlc3R0ZXN0dGVzdHRlc3AnLCAndGVzdHRlc3R0ZXN0 dGVzcScsICd0ZXN0dGVzdHRlc3R0ZXNyJywgJ3Rlc3R0ZXN0dGVzdHRlc3MnLCAndGVzdHRl c3R0ZXN0dGVzLScsICd0ZXN0dGVzdHRlc3R0ZXN1JywgJ3Rlc3R0ZXN0dGVzdHRlc3YnLCAn dGVzdHRlc3R0ZXN0dGVzdycsICd0ZXN0dGVzdHRlc3R0ZXN4JywgJ3Rlc3R0ZXN0dGVzdHRl c3knLCAndGVzdHRlc3R0ZXN0dGVzeicsICd0ZXN0dGVzdHRlc3R0ZXMxJywgJ3Rlc3R0ZXN0 dGVzdHRlczInLCAndGVzdHRlc3R0ZXN0dGVzMycsICd0ZXN0dGVzdHRlc3R0ZXM0JywgJ3Rl c3R0ZXN0dGVzdHRlczUnLCAndGVzdHRlc3R0ZXN0dGVzNicsICd0ZXN0dGVzdHRlc3R0ZXM3 JywgJ3Rlc3R0ZXN0dGVzdHRlczgnLCAndGVzdHRlc3R0ZXN0dGVzOScsICd0ZXN0dGVzdHRl c3R0ZXMwJyk7CgpTRUxFQ1QgKiBGUk9NCiAgdGVzdApXSEVSRQogIGNvbCBJTiAoJ3Rlc3R0 ZXN0dGVzdHRlc2EnLCAndGVzdHRlc3R0ZXN0dGVzYicsICd0ZXN0dGVzdHRlc3R0ZXNjJywg J3Rlc3R0ZXN0dGVzdHRlc2QnLCAndGVzdHRlc3R0ZXN0dGVzZScsICd0ZXN0dGVzdHRlc3R0 ZXNmJywgJ3Rlc3R0ZXN0dGVzdHRlc2cnLCAndGVzdHRlc3R0ZXN0dGVzaCcsICd0ZXN0dGVz dHRlc3R0ZXNpJywgJ3Rlc3R0ZXN0dGVzdHRlc2onLCAndGVzdHRlc3R0ZXN0dGVzaycsICd0 ZXN0dGVzdHRlc3R0ZXNsJywgJ3Rlc3R0ZXN0dGVzdHRlc20nLCAndGVzdHRlc3R0ZXN0dGVz bicsICd0ZXN0dGVzdHRlc3R0ZXNvJywgJ3Rlc3R0ZXN0dGVzdHRlc3AnLCAndGVzdHRlc3R0 ZXN0dGVzcScsICd0ZXN0dGVzdHRlc3R0ZXNyJywgJ3Rlc3R0ZXN0dGVzdHRlc3MnLCAndGVz dHRlc3R0ZXN0dGVzLScsICd0ZXN0dGVzdHRlc3R0ZXN1JywgJ3Rlc3R0ZXN0dGVzdHRlc3Yn LCAndGVzdHRlc3R0ZXN0dGVzdycsICd0ZXN0dGVzdHRlc3R0ZXN4JywgJ3Rlc3R0ZXN0dGVz dHRlc3knLCAndGVzdHRlc3R0ZXN0dGVzeicsICd0ZXN0dGVzdHRlc3R0ZXMxJywgJ3Rlc3R0 ZXN0dGVzdHRlczInLCAndGVzdHRlc3R0ZXN0dGVzMycsICd0ZXN0dGVzdHRlc3R0ZXM0Jywg J3Rlc3R0ZXN0dGVzdHRlczUnLCAndGVzdHRlc3R0ZXN0dGVzNicsICd0ZXN0dGVzdHRlc3R0 ZXM3JywgJ3Rlc3R0ZXN0dGVzdHRlczgnLCAndGVzdHRlc3R0ZXN0dGVzOScsICd0ZXN0dGVz dHRlc3R0ZXMwJykKICBPUgogIGNvbCBJTiAoJ3Rlc3R0ZXN0dGVzdHRlc2EyJywgJ3Rlc3R0 ZXN0dGVzdHRlc2InLCAndGVzdHRlc3R0ZXN0dGVzYycsICd0ZXN0dGVzdHRlc3R0ZXNkJywg J3Rlc3R0ZXN0dGVzdHRlc2UnLCAndGVzdHRlc3R0ZXN0dGVzZicsICd0ZXN0dGVzdHRlc3R0 ZXNnJywgJ3Rlc3R0ZXN0dGVzdHRlc2gnLCAndGVzdHRlc3R0ZXN0dGVzaScsICd0ZXN0dGVz dHRlc3R0ZXNqJywgJ3Rlc3R0ZXN0dGVzdHRlc2snLCAndGVzdHRlc3R0ZXN0dGVzbCcsICd0 ZXN0dGVzdHRlc3R0ZXNtJywgJ3Rlc3R0ZXN0dGVzdHRlc24nLCAndGVzdHRlc3R0ZXN0dGVz bycsICd0ZXN0dGVzdHRlc3R0ZXNwJywgJ3Rlc3R0ZXN0dGVzdHRlc3EnLCAndGVzdHRlc3R0 ZXN0dGVzcicsICd0ZXN0dGVzdHRlc3R0ZXNzJywgJ3Rlc3R0ZXN0dGVzdHRlcy0nLCAndGVz dHRlc3R0ZXN0dGVzdScsICd0ZXN0dGVzdHRlc3R0ZXN2JywgJ3Rlc3R0ZXN0dGVzdHRlc3cn LCAndGVzdHRlc3R0ZXN0dGVzeCcsICd0ZXN0dGVzdHRlc3R0ZXN5JywgJ3Rlc3R0ZXN0dGVz dHRlc3onLCAndGVzdHRlc3R0ZXN0dGVzMScsICd0ZXN0dGVzdHRlc3R0ZXMyJywgJ3Rlc3R0 ZXN0dGVzdHRlczMnLCAndGVzdHRlc3R0ZXN0dGVzNCcsICd0ZXN0dGVzdHRlc3R0ZXM1Jywg J3Rlc3R0ZXN0dGVzdHRlczYnLCAndGVzdHRlc3R0ZXN0dGVzNycsICd0ZXN0dGVzdHRlc3R0 ZXM4JywgJ3Rlc3R0ZXN0dGVzdHRlczknLCAndGVzdHRlc3R0ZXN0dGVzMCcpOw== --------------o5jhd1mCyohvPhca0D0ygUIS Content-Type: text/plain; charset=UTF-8; name="0001-Fixed-hashed-SAOP.patch" Content-Disposition: attachment; filename="0001-Fixed-hashed-SAOP.patch" Content-Transfer-Encoding: base64 RnJvbSA3ZGU1OTI5MjhhY2FkYTU5N2Y2ZTQzMTQwMzZhYmQ4N2E2YWY2NDAzIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBEYXZpZCBHZWllciA8Z2VpZGF2LnBnQGdtYWlsLmNv bT4KRGF0ZTogVHVlLCAxIEFwciAyMDI1IDEzOjQ3OjI3ICswMjAwClN1YmplY3Q6IFtQQVRD SF0gRml4ZWQgaGFzaGVkIFNBT1AKCi0tLQogc3JjL2JhY2tlbmQvb3B0aW1pemVyL3V0aWwv Y2xhdXNlcy5jIHwgNCArKy0tCiAxIGZpbGUgY2hhbmdlZCwgMiBpbnNlcnRpb25zKCspLCAy IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci91dGls L2NsYXVzZXMuYyBiL3NyYy9iYWNrZW5kL29wdGltaXplci91dGlsL2NsYXVzZXMuYwppbmRl eCA0M2RmZWNmYjQ3Zi4uMDVkMzM0NjExNjMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29w dGltaXplci91dGlsL2NsYXVzZXMuYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRp bC9jbGF1c2VzLmMKQEAgLTIzMjYsNyArMjMyNiw3IEBAIGNvbnZlcnRfc2FvcF90b19oYXNo ZWRfc2FvcF93YWxrZXIoTm9kZSAqbm9kZSwgdm9pZCAqY29udGV4dCkKIAkJCQkJCS8qIExv b2tzIGdvb2QuIEZpbGwgaW4gdGhlIGhhc2ggZnVuY3Rpb25zICovCiAJCQkJCQlzYW9wLT5o YXNoZnVuY2lkID0gbGVmdGhhc2hmdW5jOwogCQkJCQl9Ci0JCQkJCXJldHVybiB0cnVlOwor CQkJCQlyZXR1cm4gZmFsc2U7CiAJCQkJfQogCQkJfQogCQkJZWxzZQkJCQkvKiAhc2FvcC0+ dXNlT3IgKi8KQEAgLTIzNjQsNyArMjM2NCw3IEBAIGNvbnZlcnRfc2FvcF90b19oYXNoZWRf c2FvcF93YWxrZXIoTm9kZSAqbm9kZSwgdm9pZCAqY29udGV4dCkKIAkJCQkJCSAqLwogCQkJ CQkJc2FvcC0+bmVnZnVuY2lkID0gZ2V0X29wY29kZShuZWdhdG9yKTsKIAkJCQkJfQotCQkJ CQlyZXR1cm4gdHJ1ZTsKKwkJCQkJcmV0dXJuIGZhbHNlOwogCQkJCX0KIAkJCX0KIAkJfQot LSAKMi4zNC4xCgo= --------------o5jhd1mCyohvPhca0D0ygUIS--