> On 18/01/2023 18:56 CET Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > "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.) EXPLAIN ANALYZE shows which subplan gets executed. Look for "never executed". -- Erik
