Thread: Mysterious query plan
Hi - I mistyped a table name in the following query: select * from gazContainers where gazPlaceID in (select gazPlaceID from tipsterAuxiliary); Turns out there's no gazPlaceID column in tipsterAuxiliary, so the inner gazPlaceID was referring to the outer gazContainers table. This ran all night without finishing, before I discovered my mistake. What I can't figure out is just what the query planner came up with. Can someone explain it to me? Is it effectively some sort of horrible cross join? Here's the terse query plan: Seq Scan on gazcontainers (cost=0.00..10830971486.25 rows=2935950 width=8) Filter: (subplan) SubPlan -> Seq Scan on tipsterauxiliary (cost=0.00..3330.04 rows=143604 width=0) and appended below is the verbose output. Any explanation is very much appreciated. Thanks! - John D. Burger MITRE QUERY PLAN ------------------------------------------------------------------------ -------- {SEQSCAN :startup_cost 0.00 :total_cost 10830971486.25 :plan_rows 2935950 :plan_width 8 :targetlist ( {TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1 :resname gazplaceid :ressortgroupref 0 :resorigtbl 288092363 :resorigcol 1 :resjunk false } :expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1 } } {TARGETENTRY :resdom {RESDOM :resno 2 :restype 23 :restypmod -1 :resname containerid :ressortgroupref 0 :resorigtbl 288092363 :resorigcol 2 :resjunk false } :expr {VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2 } } ) :qual ( {SUBPLAN :subLinkType 2 :useOr false :exprs ( {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :args ( {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1 } {PARAM :paramkind 15 :paramid 1 :paramname <> :paramtype 23 } ) } ) :paramIds ( 1) :plan {SEQSCAN :startup_cost 0.00 :total_cost 3330.04 :plan_rows 143604 :plan_width 0 :targetlist ( {TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1 :resname gazplaceid :ressortgroupref 0 :resorigtbl 288092363 :resorigcol 1 :resjunk false } :expr {PARAM :paramkind 15 :paramid 0 :paramname <> :paramtype 23 } } ) :qual <> :lefttree <> :righttree <> :initPlan <> :extParam ( 0) :allParam ( 0) :nParamExec 0 :scanrelid 1 } :plan_id 4 :rtable ( {RTE :alias <> :eref {ALIAS :aliasname tipsterauxiliary :colnames ("sourceid" "name" "type" "containername1" "containertyp e1" "containername2" "containertype2" "containername3" "containert ype3" "modifier" "linenum") } :rtekind 0 :relid 298199547 :inh false :inFromCl true :checkForRead true :checkForWrite false :checkAsUser 0 } ) :useHashTable false :unknownEqFalse true :setParam () :parParam ( 0) :args ( {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1 } ) } ) :lefttree <> :righttree <> :initPlan <> :extParam () :allParam ( 1) :nParamExec 2 :scanrelid 1 }
"John D. Burger" <john@mitre.org> writes: > I mistyped a table name in the following query: > select * from gazContainers > where gazPlaceID in (select gazPlaceID from tipsterAuxiliary); > Turns out there's no gazPlaceID column in tipsterAuxiliary, so the > inner gazPlaceID was referring to the outer gazContainers table. This > ran all night without finishing, before I discovered my mistake. What > I can't figure out is just what the query planner came up with. Can > someone explain it to me? Is it effectively some sort of horrible > cross join? Here's the terse query plan: > Seq Scan on gazcontainers (cost=0.00..10830971486.25 rows=2935950 > width=8) > Filter: (subplan) > SubPlan > -> Seq Scan on tipsterauxiliary (cost=0.00..3330.04 rows=143604 > width=0) Yup. What you've got there is the naive implementation of "IN (SELECT...)", ie, "for each row of gazContainers, run the SELECT over tipsterAuxiliary and look for a match". EXPLAIN isn't amazingly bright about displaying subplan invocations, so you just see "subplan" and not anything more specific. (Somebody should try to fix that sometime.) In this case, since the first row from the sub-SELECT will invariably contain the same value the IN is seeking, we won't read any more than one row from the sub-SELECT. So the runtime wouldn't be proportional to the product of the table sizes, but it would be proportional to the size of gazContainers with some depressingly large multiplier corresponding to the startup overhead for the sub-SELECT. (I've seen people make the identical mistake with a NOT IN, and then the runtime really is proportional to the product :-() regards, tom lane