Re: Weird query plan

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(at)openratings(dot)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
>