Hello,
We noticed that after upgrading to 7.1beta[245] the execution time for
some often used queries went up by a factor of 2 or more. Considering
the early beta state I was not alarmed. But since I noticed that
yesterday's snapshot still has the problem, I'd really like to tell you
about it.
Here is one of the queries, it takes about half a second on our computer
(PII 233 with 256MB RAM) to execute and returns typically 1-4 rows via
two index scans with high selectivity. So it looks to me that planning
time outwages execution time by far. 7.0 took about 0.15 seconds (which
is still much).
Here is the query:
explain verbose select gaenge , s . artikelid , text from
schaertabelle s , extartbez e where maschine = int2(109) and
schaerdatum = '2001-01-13' and s . artikelid = e . artikelid and
extartbezid = 1 and bezkomptype = 0 order by text limit 10;
And the plan for 7.0 and 7.1 (attached).
The data and schema is accessible via
http://home.wtal.de/petig/pg_test.sql.gz
If you omit 'int2(' the index scan collapses into a sequential scan.
(Well known problem with int2 indices)
Christof
Oh, I'll attach the schema, too. So if you just want to take a look at
the table definition you don't have to download the data.
NOTICE: QUERY DUMP:
{ LIMIT :startup_cost 11.70 :total_cost 11.70 :rows 1 :width 22 :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1 :resname gaenge
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname artikelid
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname text
:reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 2
:varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 3}})
:qpqual <> :lefttree { SORT :startup_cost 11.70 :total_cost 11.70 :rows 1 :width 22
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1
:resname gaenge :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno
4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname
artikelid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname text
:reskey 1 :reskeyop 1071 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 2
:varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 3}})
:qpqual <> :lefttree { NESTLOOP :startup_cost 0.00 :total_cost 11.69 :rows 1 :width 22
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1
:resname gaenge :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 1 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname artikelid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR :varno 65001 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54
:resname text :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR
:varno 65000 :varattno 1 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2
:varoattno 3}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 2.02
:rows 1 :width 6 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno
8}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 :indxid ( 2424224) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 94 :opid 63 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype
21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST :consttype 21
:constlen 2 :constbyval true :constisnull false :constvalue 2 [ 109 0 0 0 ] })} {
EXPR :typeOid 16 :opType op :oper { OPER :opno 1093 :opid 1086 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 2 :vartype 1082 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 3} { CONST :consttype 1082 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 122 1 0 0 ] })})) :indxqualorig (({ EXPR :typeOid
16 :opType op :oper { OPER :opno 94 :opid 63 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 2 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2} {
CONST :consttype 21 :constlen 2 :constbyval true :constisnull false :constvalue 2 [
109 0 0 0 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1093 :opid 1086
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 3 :vartype 1082 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 3} { CONST :consttype 1082 :constlen 4
:constbyval true :constisnull false :constvalue 4 [ 122 1 0 0 ] })})) :indxorderdir 1
} :righttree { INDEXSCAN :startup_cost 0.00 :total_cost 9.60 :rows 6 :width 16
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 54
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2
:varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno
2}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 2 :indxid ( 2424197) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1752 :opid 1718 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 1
:vartype 1700 :vartypmod 327684 :varlevelsup 0 :varnoold 2 :varoattno 6} { CONST
:consttype 1700 :constlen -1 :constbyval false :constisnull false :constvalue 11 [ 11
0 0 0 0 0 0 0 0 0 16 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1752
:opid 1718 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 2 :vartype 1700
:vartypmod 131076 :varlevelsup 0 :varnoold 2 :varoattno 5} { CONST :consttype 1700
:constlen -1 :constbyval false :constisnull false :constvalue 10 [ 10 0 0 0 0 0 0 0 0
0 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1752 :opid
1718 :opresulttype 16 } :args ({ VAR :varno 2 :varattno 6 :vartype 1700 :vartypmod
327684 :varlevelsup 0 :varnoold 2 :varoattno 6} { CONST :consttype 1700 :constlen -1
:constbyval false :constisnull false :constvalue 11 [ 11 0 0 0 0 0 0 0 0 0 16 ] })} {
EXPR :typeOid 16 :opType op :oper { OPER :opno 1752 :opid 1718 :opresulttype 16 }
:args ({ VAR :varno 2 :varattno 5 :vartype 1700 :vartypmod 131076 :varlevelsup 0
:varnoold 2 :varoattno 5} { CONST :consttype 1700 :constlen -1 :constbyval false
:constisnull false :constvalue 10 [ 10 0 0 0 0 0 0 0 0 0 ] })})) :indxorderdir 1 }
:extprm () :locprm () :initplan <> :nprm 0 :jointype 0 :joinqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 65001
:varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8} { VAR
:varno 65000 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2
:varoattno 2})})} :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :keycount
1 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :limitOffset <>
:limitCount { CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 10 0 0 0 ] }}
NOTICE: QUERY PLAN:
Limit (cost=11.70..11.70 rows=1 width=22)
-> Sort (cost=11.70..11.70 rows=1 width=22)
-> Nested Loop (cost=0.00..11.69 rows=1 width=22)
-> Index Scan using schaertabelle_kettnr on schaertabelle s
(cost=0.00..2.02 rows=1 width=6)
-> Index Scan using extartbez_speed on extartbez e (cost=0.00..9.60
rows=6 width=16)
{ LIMIT
:startup_cost 11.70
:total_cost 11.70
:rows 1
:width 22
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname gaenge
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname artikelid
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 8
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1043
:restypmod 54
:resname text
:reskey 0
:reskeyop 0
:ressortgroupref 1
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 3
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
)
:qpqual <>
:lefttree
{ SORT
:startup_cost 11.70
:total_cost 11.70
:rows 1
:width 22
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname gaenge
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname artikelid
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 8
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1043
:restypmod 54
:resname text
:reskey 1
:reskeyop 1071
:ressortgroupref 1
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 3
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
)
:qpqual <>
:lefttree
{ NESTLOOP
:startup_cost 0.00
:total_cost 11.69
:rows 1
:width 22
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname gaenge
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 65001
:varattno 1
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname artikelid
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 65001
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1043
:restypmod 54
:resname text
:reskey 0
:reskeyop 0
:ressortgroupref 1
:resjunk false
}
:expr
{ VAR
:varno 65000
:varattno 1
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
)
:qpqual <>
:lefttree
{ INDEXSCAN
:startup_cost 0.00
:total_cost 2.02
:rows 1
:width 6
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 8
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
)
:qpqual <>
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 1
:indxid ( 2424224)
:indxqual ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 94
:opid 63
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 1
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{ CONST
:consttype 21
:constlen 2
:constbyval true
:constisnull false
:constvalue 2 [ 109 0 0 0 ]
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1093
:opid 1086
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 2
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
{ CONST
:consttype 1082
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 122 1 0 0 ]
}
)
}
)
)
:indxqualorig ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 94
:opid 63
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 2
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{ CONST
:consttype 21
:constlen 2
:constbyval true
:constisnull false
:constvalue 2 [ 109 0 0 0 ]
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1093
:opid 1086
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 3
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
{ CONST
:consttype 1082
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 122 1 0 0 ]
}
)
}
)
)
:indxorderdir 1
}
:righttree
{ INDEXSCAN
:startup_cost 0.00
:total_cost 9.60
:rows 6
:width 16
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 1043
:restypmod 54
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 3
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 2
:varoattno 2
}
}
)
:qpqual <>
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 2
:indxid ( 2424197)
:indxqual ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1752
:opid 1718
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 1
:vartype 1700
:vartypmod 327684
:varlevelsup 0
:varnoold 2
:varoattno 6
}
{ CONST
:consttype 1700
:constlen -1
:constbyval false
:constisnull false
:constvalue 11 [ 11 0 0 0 0 0 0 0 0 0 16 ]
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1752
:opid 1718
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 2
:vartype 1700
:vartypmod 131076
:varlevelsup 0
:varnoold 2
:varoattno 5
}
{ CONST
:consttype 1700
:constlen -1
:constbyval false
:constisnull false
:constvalue 10 [ 10 0 0 0 0 0 0 0 0 0 ]
}
)
}
)
)
:indxqualorig ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1752
:opid 1718
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 6
:vartype 1700
:vartypmod 327684
:varlevelsup 0
:varnoold 2
:varoattno 6
}
{ CONST
:consttype 1700
:constlen -1
:constbyval false
:constisnull false
:constvalue 11 [ 11 0 0 0 0 0 0 0 0 0 16 ]
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1752
:opid 1718
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 5
:vartype 1700
:vartypmod 131076
:varlevelsup 0
:varnoold 2
:varoattno 5
}
{ CONST
:consttype 1700
:constlen -1
:constbyval false
:constisnull false
:constvalue 10 [ 10 0 0 0 0 0 0 0 0 0 ]
}
)
}
)
)
:indxorderdir 1
}
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:jointype 0
:joinqual (
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 96
:opid 65
:opresulttype 16
}
:args (
{ VAR
:varno 65001
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
{ VAR
:varno 65000
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 2
:varoattno 2
}
)
}
)
}
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:keycount 1
}
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:limitOffset <>
:limitCount
{ CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 10 0 0 0 ]
}
}
NOTICE: QUERY DUMP:
{ SORT :startup_cost 4.48 :total_cost 4.48 :rows 1 :width 22 :state <> :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21 :restypmod -1 :resname gaenge
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname artikelid
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 8 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043 :restypmod 54 :resname text
:reskey 1 :reskeyop 1071 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 2
:varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2 :varoattno 3}})
:qpqual <> :lefttree { NESTLOOP :startup_cost 0.00 :total_cost 4.47 :rows 1 :width 22
:state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 21
:restypmod -1 :resname gaenge :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
} :expr { VAR :varno 65001 :varattno 1 :vartype 21 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23
:restypmod -1 :resname artikelid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65001 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1043
:restypmod 54 :resname text :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false }
:expr { VAR :varno 65000 :varattno 1 :vartype 1043 :vartypmod 54 :varlevelsup 0
:varnoold 2 :varoattno 3}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00
:total_cost 2.02 :rows 1 :width 6 :state <> :qptargetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 21 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 21
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 4}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid (
2073721) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 94 :opid 63
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 21 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 2} { CONST :consttype 21 :constlen 2
:constisnull false :constvalue 2 [ 109 0 0 0 ] :constbyval true })} { EXPR :typeOid
16 :opType op :oper { OPER :opno 1093 :opid 1086 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 2 :vartype 1082 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 3} { CONST :consttype 1082 :constlen 4 :constisnull false :constvalue 4 [
122 1 0 0 ] :constbyval true })})) :indxqualorig (({ EXPR :typeOid 16 :opType op
:oper { OPER :opno 94 :opid 63 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2
:vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST :consttype
21 :constlen 2 :constisnull false :constvalue 2 [ 109 0 0 0 ] :constbyval true })} {
EXPR :typeOid 16 :opType op :oper { OPER :opno 1093 :opid 1086 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 3 :vartype 1082 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 3} { CONST :consttype 1082 :constlen 4 :constisnull false
:constvalue 4 [ 122 1 0 0 ] :constbyval true })})) :indxorderdir 1 } :righttree {
INDEXSCAN :startup_cost 0.00 :total_cost 2.44 :rows 1 :width 16 :state <>
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 54
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 3 :vartype 1043 :vartypmod 54 :varlevelsup 0 :varnoold 2
:varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
:resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 2 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno
2}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1752 :opid 1718
:opresulttype 16 } :args ({ VAR :varno 2 :varattno 6 :vartype 1700 :vartypmod 327684
:varlevelsup 0 :varnoold 2 :varoattno 6} { CONST :consttype 1700 :constlen -1
:constisnull false :constvalue 11 [ 11 0 0 0 0 0 0 0 0 0 16 ] :constbyval false })}
{ EXPR :typeOid 16 :opType op :oper { OPER :opno 1752 :opid 1718 :opresulttype 16 }
:args ({ VAR :varno 2 :varattno 5 :vartype 1700 :vartypmod 131076 :varlevelsup 0
:varnoold 2 :varoattno 5} { CONST :consttype 1700 :constlen -1 :constisnull false
:constvalue 10 [ 10 0 0 0 0 0 0 0 0 0 ] :constbyval false })}) :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 2 :indxid (
2133411) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 2 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 2 :varoattno 2} { VAR :varno 65001 :varattno 2 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8})})) :indxqualorig (({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR
:varno 65001 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8} { VAR :varno 2 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 2 :varoattno 2})})) :indxorderdir 0 } :extprm () :locprm () :initplan <>
:nprm 0 } :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :nonameid 0
:keycount 1 }
NOTICE: QUERY PLAN:
Sort (cost=4.48..4.48 rows=1 width=22)
-> Nested Loop (cost=0.00..4.47 rows=1 width=22)
-> Index Scan using schaertabelle_kettnr on schaertabelle s (cost=0.00..2.02
rows=1 width=6)
-> Index Scan using extartbez_artikelid on extartbez e (cost=0.00..2.44
rows=1 width=16)
{ SORT
:startup_cost 4.48
:total_cost 4.48
:rows 1
:width 22
:state <>
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname gaenge
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname artikelid
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 8
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1043
:restypmod 54
:resname text
:reskey 1
:reskeyop 1071
:ressortgroupref 1
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 3
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
)
:qpqual <>
:lefttree
{ NESTLOOP
:startup_cost 0.00
:total_cost 4.47
:rows 1
:width 22
:state <>
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname gaenge
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 65001
:varattno 1
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname artikelid
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 65001
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 3
:restype 1043
:restypmod 54
:resname text
:reskey 0
:reskeyop 0
:ressortgroupref 1
:resjunk false
}
:expr
{ VAR
:varno 65000
:varattno 1
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
)
:qpqual <>
:lefttree
{ INDEXSCAN
:startup_cost 0.00
:total_cost 2.02
:rows 1
:width 6
:state <>
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 21
:restypmod -1
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 8
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
)
:qpqual <>
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 1
:indxid ( 2073721)
:indxqual ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 94
:opid 63
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 1
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{ CONST
:consttype 21
:constlen 2
:constisnull false
:constvalue 2 [ 109 0 0 0 ]
:constbyval true
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1093
:opid 1086
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 2
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
{ CONST
:consttype 1082
:constlen 4
:constisnull false
:constvalue 4 [ 122 1 0 0 ]
:constbyval true
}
)
}
)
)
:indxqualorig ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 94
:opid 63
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 2
:vartype 21
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{ CONST
:consttype 21
:constlen 2
:constisnull false
:constvalue 2 [ 109 0 0 0 ]
:constbyval true
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1093
:opid 1086
:opresulttype 16
}
:args (
{ VAR
:varno 1
:varattno 3
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
{ CONST
:consttype 1082
:constlen 4
:constisnull false
:constvalue 4 [ 122 1 0 0 ]
:constbyval true
}
)
}
)
)
:indxorderdir 1
}
:righttree
{ INDEXSCAN
:startup_cost 0.00
:total_cost 2.44
:rows 1
:width 16
:state <>
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 1043
:restypmod 54
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 3
:vartype 1043
:vartypmod 54
:varlevelsup 0
:varnoold 2
:varoattno 3
}
}
{ TARGETENTRY
:resdom
{ RESDOM
:resno 2
:restype 23
:restypmod -1
:resname <>
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 2
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 2
:varoattno 2
}
}
)
:qpqual (
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1752
:opid 1718
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 6
:vartype 1700
:vartypmod 327684
:varlevelsup 0
:varnoold 2
:varoattno 6
}
{ CONST
:consttype 1700
:constlen -1
:constisnull false
:constvalue 11 [ 11 0 0 0 0 0 0 0 0 0 16 ]
:constbyval false
}
)
}
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1752
:opid 1718
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 5
:vartype 1700
:vartypmod 131076
:varlevelsup 0
:varnoold 2
:varoattno 5
}
{ CONST
:consttype 1700
:constlen -1
:constisnull false
:constvalue 10 [ 10 0 0 0 0 0 0 0 0 0 ]
:constbyval false
}
)
}
)
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 2
:indxid ( 2133411)
:indxqual ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 96
:opid 65
:opresulttype 16
}
:args (
{ VAR
:varno 2
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 2
:varoattno 2
}
{ VAR
:varno 65001
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
)
}
)
)
:indxqualorig ((
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 96
:opid 65
:opresulttype 16
}
:args (
{ VAR
:varno 65001
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
{ VAR
:varno 2
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 2
:varoattno 2
}
)
}
)
)
:indxorderdir 0
}
:extprm ()
:locprm ()
:initplan <>
:nprm 0
}
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:nonameid 0
:keycount 1
}
CREATE TABLE "schaertabelle" (
"entryid" integer DEFAULT nextval('schaertabelle_entryid_seq'::text) NOT NULL,
"maschine" smallint NOT NULL,
"schaerdatum" date NOT NULL,
"gaenge" smallint NOT NULL,
"menge" integer NOT NULL,
"status" smallint NOT NULL,
"jahrgang" numeric(4,0) NOT NULL,
"artikelid" integer NOT NULL,
Constraint "schaertabelle_pkey" Primary Key ("entryid")
);
CREATE INDEX "schaertabelle_kettnr" on "schaertabelle" using btree ( "maschine"
"int2_ops", "schaerdatum" "date_ops" );
CREATE INDEX "schaertabelle_speed" on "schaertabelle" using btree ( "artikelid"
"int4_ops", "jahrgang" "numeric_ops" );
CREATE TABLE "extartbez" (
"kundennr" numeric(5,0),
"artikelid" integer NOT NULL,
"text" character varying(50) NOT NULL,
"stand" timestamp with time zone DEFAULT now(),
"bezkomptype" numeric(2,0),
"extartbezid" numeric(5,0)
);
CREATE UNIQUE INDEX "extartbez_uniq" on "extartbez" using btree ( "kundennr"
"numeric_ops", "extartbezid" "numeric_ops", "artikelid" "int4_ops", "bezkomptype"
"numeric_ops" );
CREATE INDEX "extartbez_artikelid" on "extartbez" using btree ( "artikelid"
"int4_ops" );
CREATE INDEX "extartbez_speed" on "extartbez" using btree ( "extartbezid"
"numeric_ops", "bezkomptype" "numeric_ops", "text" "varchar_ops" );
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
