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 1u0L44-00GnfD-Se for pgsql-hackers@arkaria.postgresql.org; Thu, 03 Apr 2025 13:57:05 +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 1u0L43-007j7f-1e for pgsql-hackers@arkaria.postgresql.org; Thu, 03 Apr 2025 13:57:03 +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.94.2) (envelope-from ) id 1u0L42-007j7X-Nx for pgsql-hackers@lists.postgresql.org; Thu, 03 Apr 2025 13:57:02 +0000 Received: from forward501d.mail.yandex.net ([2a02:6b8:c41:1300:1:45:d181:d501]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u0L3z-002kDQ-0V for pgsql-hackers@lists.postgresql.org; Thu, 03 Apr 2025 13:57:01 +0000 Received: from mail-nwsmtp-smtp-production-main-63.klg.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-63.klg.yp-c.yandex.net [IPv6:2a02:6b8:c42:3f4a:0:640:9ef9:0]) by forward501d.mail.yandex.net (Yandex) with ESMTPS id EDABF610B0; Thu, 3 Apr 2025 16:56:54 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-63.klg.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id ruYRZKULouQ0-mxkTKxkR; Thu, 03 Apr 2025 16:56:54 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tantorlabs.com; s=mail; t=1743688614; bh=n/bSeJV7fbBrO+GPWWWgx8E2qnRUQSCFSTPdi3DHfRM=; h=From:In-Reply-To:Cc:Date:References:To:Subject:Message-ID; b=r5AGgYPFyL+2Tr5CD3K8y6N1bl1UGWLumzBPqQEysrb2UZDMlGqzTqvcVpjWsSi2R MktwUqOq693sG+fi1OIY2cCD+/RVcaqhppixaypJk2oD3v7YfblOzi0Ut1xTFsvsYA DpSO6lnxhm44C2mcDfe0/8nH5tLNL5g2lesO9Fm4= Authentication-Results: mail-nwsmtp-smtp-production-main-63.klg.yp-c.yandex.net; dkim=pass header.i=@tantorlabs.com Message-ID: <32446f52-1b18-499a-a77b-d6b66ad0f4cf@tantorlabs.com> Date: Thu, 3 Apr 2025 16:56:52 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query To: Alena Rybakina , PostgreSQL Hackers , Peter Petrov Cc: Ranier Vilela 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> Content-Language: en-US From: Ilia Evdokimov In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 02.04.2025 19:39, Alena Rybakina wrote: > > I see that I need to add a walker that, when traversing the tree, > determines whether there are conditions under which pull-up is > impossible - the presence of > volatility of functions and other restrictions, and leave the > transformation for the var objects that I added before, I described it > here. > I have some concerns about pulling up every clause from the subquery with one column. In particular, not every clause is safe or beneficial to pull up: OR-clauses, CASE expressions, nested sublinks could significantly change how the planner estimates the number of rows or applies filters, especially when they are not true join predicates. Pulling them up might lead to worse plans, or even change the semantics in subtle ways. I think before applying such transformations, we should make sure they are not only safe but actually improve the resulting plan.