Re: Weird query plan - Mailing list pgsql-general
From | Dmitry Tkach |
---|---|
Subject | Re: Weird query plan |
Date | |
Msg-id | 3F68C69F.5050506@openratings.com Whole thread Raw |
In response to | Re: Weird query plan (Dmitry Tkach <dmitry@openratings.com>) |
Responses |
Re: Weird query plan
Re: Weird query plan |
List | pgsql-general |
Here is that verbose output, if it is of any help: prod=# explain verbose select a.id from a, b where a.id >= 7901288 and b.id=a.id limit 1; NOTICE: QUERY DUMP: { LIMIT :startup_cost 0.00 :total_cost 12.78 :rows 1 :width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree { NESTLOOP :startup_cost 0.00 :total_cost 1023061272.15 :rows 80049919 :width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 380070641.01 :rows 81786784 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 2 :indxid ( 708140136) :indxqual (<>) :indxqualorig (<>) :indxorderdir 1 } :righttree { INDEXSCAN :startup_cost 0.00 :total_cost 6.86 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1074605180) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1})} { EXPR :typeOid 16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 104 -112 120 0 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})} { EXPR :typeOid 16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 104 -112 120 0 ] })})) :indxorderdir 0 } :extprm () :locprm () :initplan <> :nprm 0 :jointype 0 :joinqual <>} :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :limitOffset <> :limitCount { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} NOTICE: QUERY PLAN: Limit (cost=0.00..12.78 rows=1 width=8) -> Nested Loop (cost=0.00..1023061272.15 rows=80049919 width=8) -> Index Scan using b_pkey on b (cost=0.00..380070641.01 rows=81786784 width=4) -> Index Scan using a_pkey on a (cost=0.00..6.86 rows=1 width=4) EXPLAIN prod=# explain verbose select a.id from a,b where b.id >= 7901288 and b.id=a.id limit 1; NOTICE: QUERY DUMP: { LIMIT :startup_cost 0.00 :total_cost 12.51 :rows 1 :width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree { NESTLOOP :startup_cost 0.00 :total_cost 1009772807.91 :rows 80740598 :width 8 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname duns :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 375410773.29 :rows 80740598 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 2 :indxid ( 708140136) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 104 -112 120 0 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 525 :opid 150 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 104 -112 120 0 ] })})) :indxorderdir 1 } :righttree { INDEXSCAN :startup_cost 0.00 :total_cost 6.85 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1074605180) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1})})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 65001 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno 1} { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})})) :indxorderdir 0 } :extprm () :locprm () :initplan <> :nprm 0 :jointype 0 :joinqual <>} :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :limitOffset <> :limitCount { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] }} NOTICE: QUERY PLAN: Limit (cost=0.00..12.51 rows=1 width=8) -> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8) -> Index Scan using b_pkey on b (cost=0.00..375410773.29 rows=80740598 width=4) -> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4) EXPLAIN Dmitry Tkach wrote: > Tom Lane wrote: > >> Dmitry Tkach <dmitry@openratings.com> writes: >> >> >>> I now have the same problem with it, but the other way around - the >>> query with a condition on a runs quickly, and one with a condition >>> on b does not... and the query plans are the same, and have a as >>> outer table... >>> >> >> >> Hm. You know, I wonder whether the query plans really are the same. >> One of the reasons why 7.3 and later show the qual conditions is that >> it was too easy to fall into the trap of assuming that plans of the same >> structure had the same conditions. Try doing "explain verbose" and look >> to see if the plans look the same at that level of detail. >> >> regards, tom lane >> >> > I am afraid that's too much detail for me to look at :-) > I have no clue what all that stuff means :-( > > But I am pretty sure they are the same - I did not know whether the > first one or the second was supposed to be the outer relation, so I > looked at it in the debugger... > So, I know that in both cases it was using b for the outer loop... > > Thanks! > Dima >
pgsql-general by date: