Re: Weird query plan - Mailing list pgsql-general

There isn't a formatter for these plans is there? Or some template for a
unix editor that will format it?

Dmitry Tkach wrote:

> 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
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>