"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