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.96) (envelope-from ) id 1wMJ4n-002N6T-0F for pgsql-hackers@arkaria.postgresql.org; Mon, 11 May 2026 05:21:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wMJ4l-00G2Bv-21 for pgsql-hackers@arkaria.postgresql.org; Mon, 11 May 2026 05:21:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wMJ4l-00G2Bm-14 for pgsql-hackers@lists.postgresql.org; Mon, 11 May 2026 05:21:07 +0000 Received: from mail-qv1-xf43.google.com ([2607:f8b0:4864:20::f43]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wMJ4i-00000001ECt-3UOE for pgsql-hackers@lists.postgresql.org; Mon, 11 May 2026 05:21:06 +0000 Received: by mail-qv1-xf43.google.com with SMTP id 6a1803df08f44-8bc379fcb51so1306936d6.0 for ; Sun, 10 May 2026 22:21:05 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1778476864; cv=none; d=google.com; s=arc-20240605; b=cZJL+q4ntVE/y0MaUs/n0QsKwCADHkCTghEqpvmEsrLcozs3AO+VeEo56+zRrS6F3A 5SkaASnU5b6g2mXVs7BQ7RSOaCQOANDN9Xg0D4yFZ06LB4Xtkej8+1RWVXimHBRT4atj ulK5fVTM9Jw6BrcBIUL/e6LRvIAOEeEl+cYFWoHXDKr/4v5mE9ugCvmfwpsni6yRv2FJ sXJY1Vg/L51Gmj8LeOX0BpAI35ZG+JHyrxhPhEw+R0S1gKuI+mEPOSeq4m8UYXbrpEux f9cEcyNGPbxYKlK/SfTZR3cDGxYA0N/CEcFgfn0bFTnPw8fLtFauzbjMdvYhcAAQAumK M4og== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=raTQoPICmOFY09NJzHJ1YAgAZTOGIEuAY5pZDo2O3rg=; fh=UbsYkjQAAhjWq1MKnO2WdFBhV1swRsN2OFY3tvtCdSc=; b=V0X8B5ROtkH2QIOVy+zI4bPqN1f78ibaSEjeyOGDG0XM1JS5qjfru6QboiWX+hxk9t q9FxjiDnI3Yh+MvAls+tgcHIO7h6YveQb3LX4NgcHJ7xVHJ7QNkND9dPr+be1dsUEtlx FzUanqgPc/SAJjIcyPGjv1fKEvCTVxSEspFqC8jN/5cAWnsoIqH8Xe4z2IDujG+KIBeN t5uebzvFmpKzDpoxDsCzQBL2YKE8yBlbroiiGromwIUKSWaQrzuexY9G9vqiwsHi+5Oc BmXQIB6zI7lt5w4W/s/KaixsPm031xLY8emSyk3Wt01fVgkW6Y/+HevEt1+pjIGRJZap 4p6w==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1778476864; x=1779081664; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=raTQoPICmOFY09NJzHJ1YAgAZTOGIEuAY5pZDo2O3rg=; b=gfiPsJ780CKg5rlwFEhGb8yKbYb7vnkdHHJqyGiaSkrd5oR8bkQYH9YzV07A2rvqPi mOSZpnJ3dYOEjP5gakeNT4N3dggiijZiQEua7YeWt6pEbBHfxdbkXhY13l+/NdfTzOmb r37mGc0JwFkjo8wsuzb/vCvx9Tol1MTp+/m91B51anmK2auFByXoSqj4aKmWa1R1UOs7 Wu3ZgP/KzFzDpQgtNFdyp6WhgPnkKUqNWp7H2MItYER/bYViR8ThgmXrHk6QDNQd6Qzk N/K0p5ZXWL8ZHtv18Qpm2ujVh+uJEsPawrBssPnThh9wP6AWcVDOt5q4hjZ6DbqVGRgm zgEA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1778476864; x=1779081664; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=raTQoPICmOFY09NJzHJ1YAgAZTOGIEuAY5pZDo2O3rg=; b=cSDgfbhXV4scZr4/lGPuXo35cwPxM5kXeCZTq3g9bBdbHY4AxdwwoxmViHBRqg5fOu fo9WbxRqdVndByKcBJqS4u5dZJkxgYbquazXwm+VWPhdylZ1dyNDXewibtcOiOW1pU+r Grdm6jiIegTxmdDpJVgh45nYTw3efg3f0Bd7WiUHFDHiuSwhIaCIWXAw9oMRxh1Eu0te LK/bOydHUcaknea3UmeOWHYYYScYVdyNCnuZLIFavT3UISG9kAoa+qkkPqpG5v3ak2px chRLJnu+RGtMGoRHfbsgXXfwh8exDpiN37w/2gmh1HHex9c3kC7wg7366FzERlfOfRlo WSeg== X-Forwarded-Encrypted: i=1; AFNElJ8xkc8zXTLrP0rpzpjHVsZj6S4+cqLESvpjru9xcXCkyp1oNzGmWjPCGPDxofno0GB/3+xq8G37iXL9PCtB@lists.postgresql.org X-Gm-Message-State: AOJu0YwEqNKC2eJGMdfy7iNm8CaSOh2nvW95tdAiSSVdOqTz/QGb6a/d cPM+D4UOrdjkIqKw3xs4VfNtpjXJtAl8peYUCqns1GOQ9ZYWM70jPYWUAGAKjxDrDLUPF59CnaG a3E3zViwiAO4jWgKveVXbzpxErWUJdX4= X-Gm-Gg: Acq92OG4EYAxqweUXxZnoH20zxHpI+tcxI97JDMA0mjYHVPsNnDY049HrK/wLt25l3/ Q+NSC2O7aOhS7+3xPLR+Cf6FVxRrdyaJo7FTTLJImPZBFTPe7duNepIbiWoMuPu5R5WbjsUJrVw cDtNG5FPmtXxE7BALQgPltdZEJHdOmeqasCUe0j0NP9Z2Q4/3Ll3Ain4gJA5T72/vtwD5vVu9aj gxYgqpD7CGK1DIQumjVDAOcJUMUYHtCsogFD5U97oqptAAKzv6WrR4rOAvZ+Scvfo0jO5KUAoNH dCveQ3Y= X-Received: by 2002:ad4:5c6d:0:b0:8ac:a797:ba39 with SMTP id 6a1803df08f44-8bc429761e9mr249983476d6.2.1778476863963; Sun, 10 May 2026 22:21:03 -0700 (PDT) MIME-Version: 1.0 References: <0b1f670d-b39d-4966-bf32-f0d502ebc564@postgrespro.ru> <46bc4eaf-58c5-42ab-8041-d3380a0768de@postgrespro.ru> <9b041978-06e3-4a50-8a5d-dacbb054f23e@tantorlabs.com> <8d7cbbf6-ddc0-4696-8af2-a68d740e14f2@postgrespro.ru> <6078f7e1-4b29-4b31-bd28-b84149e404e4@postgrespro.ru> <975a3736-a8b5-49b3-8009-4d4e86867aa1@postgrespro.ru> <0adbf75a-2059-4bb7-b878-c33892dbc1f0@tantorlabs.com> <300d7bb0-345c-4c0b-a0ef-4de573fcc94b@postgrespro.ru> <32446f52-1b18-499a-a77b-d6b66ad0f4cf@tantorlabs.com> <87a6fcbf-929a-408a-9471-28be3080254a@postgrespro.ru> <4e9d279c-83c1-42d3-b74d-7366ecd7f085@tantorlabs.com> <386165c3-3c01-4a85-b618-7919e64a52de@postgrespro.ru> <6b0da757-c63e-4abd-8fda-4f38e7dfd222@postgrespro.ru> In-Reply-To: <6b0da757-c63e-4abd-8fda-4f38e7dfd222@postgrespro.ru> From: solaimurugan vellaipandiyan Date: Mon, 11 May 2026 10:50:20 +0530 X-Gm-Features: AVHnY4Jk_wnVzCbFQ9-py_6idthvl3fMt5cL4DbpqLjaN015qCBK4w2SSymCEN0 Message-ID: Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query To: Petr Petrov Cc: Alena Rybakina , Ilia Evdokimov , David Rowley , Ranier Vilela , PostgreSQL Hackers Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Alena, I did some additional testing on the latest patch and the behavior looks good in my environment so far. I verified the original EXISTS pull-up case and confirmed that after applying the patch the planner generates optimized Semi Join plans instead of EXISTS(SubPlan). I also tested several additional cases discussed in the thread, including: 1.NOT EXISTS queries 2. constant qual conditions 3. ANY array conditions 4. LEFT JOIN cases 5. FULL JOIN cases For the FULL JOIN scenarios, I checked both the execution plans and actual query results. In one case the planner simplified the query into a Hash Join plan, but the final query results remained correct. I additionally ran: - make check - make -C contrib check Both completed successfully in my environment and I did not observe planner crashes or incorrect query results during testing. Overall, the patch seems to improve planner behavior consistently for these EXISTS pull-up scenarios. Regards, Solaimurugan V