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 1pICg7-0005qs-VK for pgsql-general@arkaria.postgresql.org; Wed, 18 Jan 2023 17:56:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pICg6-0005Fy-2F for pgsql-general@arkaria.postgresql.org; Wed, 18 Jan 2023 17:56:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pICg5-0005Fp-Np for pgsql-general@lists.postgresql.org; Wed, 18 Jan 2023 17:56:49 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pICg3-0005Tl-PO for pgsql-general@lists.postgresql.org; Wed, 18 Jan 2023 17:56:49 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 30IHukZs3635302; Wed, 18 Jan 2023 12:56:46 -0500 From: Tom Lane To: "Dirschel, Steve" cc: "pgsql-general@lists.postgresql.org" Subject: Re: Interpreting postgres execution plan along with AND/OR precedence In-reply-to: References: Comments: In-reply-to "Dirschel, Steve" message dated "Wed, 18 Jan 2023 17:03:21 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3635300.1674064605.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Wed, 18 Jan 2023 12:56:46 -0500 Message-ID: <3635301.1674064606@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk "Dirschel, Steve" writes: > Filter: ... (alternatives: SubPlan 1 or hashed SubPlan 2) ... > SubPlan 1 > -> Index Scan using ix_lm_cc on lm_queue lmq2 (cost=3D0.40.= .177.93 rows=3D1 width=3D0) > Index Cond: ((collection_name)::text =3D (lmq1.collecti= on_name)::text) > Filter: ((lm_id <> '0'::numeric) AND ((process_type)::t= ext <> 'EXTRACT'::text)) > SubPlan 2 > -> Seq Scan on lm_queue lmq2_1 (cost=3D0.00..124999.06 rows= =3D12 width=3D32) > Filter: ((lm_id <> '0'::numeric) AND ((process_type)::t= ext <> 'EXTRACT'::text)) > I understand SubPlan 1 above- it is joining into the NOT EXISTS via the > lmq1.COLLECTION_NAME =3D lmq2.COLLECTION_NAME and then applying the othe= r > filtering inside the NOT EXISTS. But I don't understand SubPlan 2. > Given the filter conditions under SubPlan 2 it is also coming from the > NOT EXISTS because that is where LM_ID <> 0 and PROCESS_TYPE <> EXTRACT > exist but I don't understand the scenario where this query would need to > use SubPlan 2. Would anyone be able to explain under what condition(s) > SubPlan 2 would get executed? The key is the "alternatives:" notation. SubPlan 1 can be used in a "retail" fashion by invoking it once per outer row, passing a new value of lmq1.collection_name each time. SubPlan 2 is meant to be invoked just once, and its output (ie, all the relevant values of lmq2.collection_name) will be loaded into an in-memory hash table which is then probed for each outer row. At the point where these subplans are made, we don't have enough information about how many outer rows there will be to decide which way is better, so we create both subplans and postpone the decision till execution. That's all just related to the EXISTS clause, though. (Since v14 we don't do it like that anymore, so that this confusing EXPLAIN notation is gone.) > I'm trying to understand the precedence of AND/OR operations when > everything is not tied together with ()'s. The OR is lower priority than all the ANDs, so yeah moving some clauses to be after the OR would change the semantics. I think you probably need some more parentheses here; it's not clear exactly what semantics you are after. regards, tom lane