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 <dm****@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