"Dirschel, Steve" <steve.dirschel@xxxxxxxxxxxxxxxxxx> 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
