From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dirschel, Steve <steve.dirschel@thomsonreuters.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Interpreting postgres execution plan along with AND/OR precedence
Date: Wed, 18 Jan 2023 12:56:46 -0500
Message-ID: <3635301.1674064606@sss.pgh.pa.us> (raw)
In-Reply-To: <DM6PR03MB4332C3DCEE3692D67D71DA4EFAC79@DM6PR03MB4332.namprd03.prod.outlook.com>
References: <DM6PR03MB4332C3DCEE3692D67D71DA4EFAC79@DM6PR03MB4332.namprd03.prod.outlook.com>
"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
> Filter: ... (alternatives: SubPlan 1 or hashed SubPlan 2) ...
> SubPlan 1
> -> Index Scan using ix_lm_cc on lm_queue lmq2 (cost=0.40..177.93 rows=1 width=0)
> Index Cond: ((collection_name)::text = (lmq1.collection_name)::text)
> Filter: ((lm_id <> '0'::numeric) AND ((process_type)::text <> 'EXTRACT'::text))
> SubPlan 2
> -> Seq Scan on lm_queue lmq2_1 (cost=0.00..124999.06 rows=12 width=32)
> Filter: ((lm_id <> '0'::numeric) AND ((process_type)::text <> 'EXTRACT'::text))
> I understand SubPlan 1 above- it is joining into the NOT EXISTS via the
> lmq1.COLLECTION_NAME = lmq2.COLLECTION_NAME and then applying the other
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, steve.dirschel@thomsonreuters.com
Subject: Re: Interpreting postgres execution plan along with AND/OR precedence
In-Reply-To: <3635301.1674064606@sss.pgh.pa.us>
References: <DM6PR03MB4332C3DCEE3692D67D71DA4EFAC79@DM6PR03MB4332.namprd03.prod.outlook.com> <3635301.1674064606@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox