From: Erik Wienhold <ewie@ewie.name>
To: 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 19:14:12 +0100 (CET)
Message-ID: <521374201.40360.1674065652451@office.mailbox.org> (raw)
In-Reply-To: <3635301.1674064606@sss.pgh.pa.us>
References: <DM6PR03MB4332C3DCEE3692D67D71DA4EFAC79@DM6PR03MB4332.namprd03.prod.outlook.com>
<3635301.1674064606@sss.pgh.pa.us>
> On 18/01/2023 18:56 CET Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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.)
EXPLAIN ANALYZE shows which subplan gets executed. Look for "never executed".
--
Erik
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: ewie@ewie.name, tgl@sss.pgh.pa.us, steve.dirschel@thomsonreuters.com
Subject: Re: Interpreting postgres execution plan along with AND/OR precedence
In-Reply-To: <521374201.40360.1674065652451@office.mailbox.org>
References: <DM6PR03MB4332C3DCEE3692D67D71DA4EFAC79@DM6PR03MB4332.namprd03.prod.outlook.com> <3635301.1674064606@sss.pgh.pa.us> <521374201.40360.1674065652451@office.mailbox.org>
* 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