Re: Interpreting postgres execution plan along with AND/OR precedence

> On 18/01/2023 18:56 CET Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)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