pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Robert.Farrugia@go.com.mt
To: tgl@sss.pgh.pa.us
Cc: pgsql-admin@postgresql.org
Subject: Re: After VACUUM, statistics become skewed
Date: Fri, 23 May 2003 11:37:30 +0200
Message-ID: <20030523093733.0F9AC924DF1@developer.postgresql.org> (raw )
Tom,
I deleted the statistics from the pg_stats table and ran the following.
CDR=# explain analyze verbose select answertime::date, count(*),
sum(callduration) from mobileorig_200302 where answertime between
'2003/2/3 00:00:00' and '2003/2/14 23:59:59' and dialleddigits_value =
'50043992' and callednumber_type in ('P', 'M') group by answertime::date;
NOTICE: QUERY DUMP:
{ AGG :startup_cost 217970.15 :total_cost 217970.18 :rows 1 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1082
:restypmod -1 :resname answertime :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 0 :varattno 3 :vartype 1082 :vartypmod
-1 :varlevelsup 0 :varnoold 0 :varoattno 0}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 20 :restypmod -1 :resname count :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname
count :basetype 0 :aggtype 20 :target { CONST :consttype 23 :constlen 4
:constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] } :aggstar
true :aggdistinct false }} { TARGETENTRY :resdom { RESDOM :resno 3
:restype 20 :restypmod -1 :resname sum :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname sum :basetype
23 :aggtype 20 :target { VAR :varno 0 :varattno 2 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 17} :aggstar false :aggdistinct
false }}) :qpqual <> :lefttree { GRP :startup_cost 217970.15 :total_cost
217970.16 :rows 3 :width 12 :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 1184 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 1
:vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 0 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 17}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1082
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 0 :varattno 3 :vartype 1082 :vartypmod
-1 :varlevelsup 0 :varnoold 0 :varoattno 0}}) :qpqual <> :lefttree { SORT
:startup_cost 217970.15 :total_cost 217970.15 :rows 3 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 17
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 17}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1082 :restypmod -1 :resname
<> :reskey 1 :reskeyop 1095 :ressortgroupref 0 :resjunk false } :expr {
EXPR :typeOid 1082 :opType func :oper { FUNC :funcid 1178 :functype 1082
} :args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8})}}) :qpqual <> :lefttree {
INDEXSCAN :startup_cost 0.00 :total_cost 217970.14 :rows 3 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 17
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 17}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1082 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR
:typeOid 1082 :opType func :oper { FUNC :funcid 1178 :functype 1082 }
:args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 8})}}) :qpqual ({ EXPR :typeOid 16 :opType op
:oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno
1 :varattno 13 :vartype 1043 :vartypmod 35 :varlevelsup 0 :varnoold 1
:varoattno 13} { CONST :consttype 1043 :constlen -1 :constbyval false
:constisnull false :constvalue 12 [ 12 0 0 0 53 48 48 52 51 57 57 50 ]
})} { EXPR :typeOid 16 :opType or :oper <> :args ({ EXPR :typeOid 16
:opType op :oper { OPER
:opno 1054 :opid 1048 :opresulttype 16 } :args ({ VAR :varno 1 :varattno
20 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold 1 :varoattno 20} {
CONST :consttype 1042 :constlen -1 :constbyval false :constisnull false
:constvalue 5 [ 5 0 0 0 80 ] })} { EXPR :typeOid 16 :opType op :oper {
OPER :opno 1054 :opid 1048 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 20 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold 1
:varoattno 20} { CONST :consttype 1042 :constlen -1 :constbyval false
:constisnull false :constvalue 5 [ 5 0 0 0 77 ] })})}) :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:indxid ( 2591699101) :indxqual (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1325 :opid 2056 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8} { CONST :consttype 1184 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ 0 0 0 -64 125 65 -105 65 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 1323 :opid 2055 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8} { CONST :consttype 1184 :constlen
8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 -68 -59 -128
-105 65 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1325 :opid 2056 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8} { CONST :consttype 1184 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ 0 0 0 -64 125 65 -105 65 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 1323 :opid 2055 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8} { CONST :consttype 1184 :constlen
8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 -68 -59 -128
-105 65 ] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :keycount 1 } :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :numCols 1 :tuplePerGroup true } :righttree <>
:extprm () :locprm () :initplan <> :nprm 0 }
NOTICE: QUERY PLAN:
Aggregate (cost=217970.15..217970.18 rows=1 width=12) (actual
time=420082.30..420085.05 rows=7 loops=1)
-> Group (cost=217970.15..217970.16 rows=3 width=12) (actual
time=420082.24..420084.14 rows=417 loops=1)
-> Sort (cost=217970.15..217970.15 rows=3 width=12) (actual
time=420082.22..420082.60 rows=417 loops=1)
-> Index Scan using mo_200302_calling_idx on
mobileorig_200302 (cost=0.00..217970.14 rows=3 width=12) (actual
time=2965.92..420078.94 rows=417 loops=1)
Total runtime: 420085.34 msec
EXPLAIN
I then ran analyze on the table and reran the same query.
CDR=# explain analyze verbose select answertime::date, count(*),
sum(callduration) from mobileorig_200302 where answertime between
'2003/2/3 00:00:00' and '2003/2/14 23:59:59' and dialleddigits_value =
'50043992' and callednumber_type in ('P', 'M') group by answertime::date;
NOTICE: QUERY DUMP:
{ AGG :startup_cost 9265841.46 :total_cost 9265841.63 :rows 2 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1082
:restypmod -1 :resname answertime :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 0 :varattno 3 :vartype 1082 :vartypmod
-1 :varlevelsup 0 :varnoold 0 :varoattno 0}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 20 :restypmod -1 :resname count :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname
count :basetype 0 :aggtype 20 :target { CONST :consttype 23 :constlen 4
:constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] } :aggstar
true :aggdistinct false }} { TARGETENTRY :resdom { RESDOM :resno 3
:restype 20 :restypmod -1 :resname sum :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname sum :basetype
23 :aggtype 20 :target { VAR :varno 0 :varattno 2 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 17} :aggstar false :aggdistinct
false }}) :qpqual <> :lefttree { GRP :startup_cost 9265841.46 :total_cost
9265841.52 :rows 24 :width 12 :qptargetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :restype 1184 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 0
:varattno 1 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 0 :varattno 2 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 17}} { TARGETENTRY :resdom {
RESDOM :resno 3 :restype 1082 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 0
:varattno 3 :vartype 1082 :vartypmod -1 :varlevelsup 0 :varnoold 0
:varoattno 0}}) :qpqual <> :lefttree { SORT :startup_cost 9265841.46
:total_cost 9265841.46 :rows 24 :width 12 :qptargetlist ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 1184 :restypmod -1 :resname <> :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 17 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 17}} { TARGETENTRY :resdom {
RESDOM :resno 3 :restype 1082 :restypmod -1 :resname <> :reskey 1
:reskeyop 1095 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid
1082 :opType func :oper { FUNC :funcid 1178 :functype 1082 } :args ({ VAR
:varno 1 :varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 8})}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00
:total_cost 9265840.92 :rows 24 :width 12 :qptargetlist ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 1184 :restypmod -1 :resname <> :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 17 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 17}} { TARGETENTRY :resdom {
RESDOM :resno 3 :restype 1082 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR :typeOid 1082
:opType func :oper { FUNC :funcid 1178 :functype 1082 } :args ({ VAR
:varno 1 :varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 8})}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER
:opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno
13 :vartype 1043 :vartypmod 35 :varlevelsup 0 :varnoold 1 :varoattno 13}
{ CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false
:constvalue 12 [ 12 0 0 0 53 48 48 52 51 57 57 50 ] })} { EXPR :typeOid
16 :opType or :oper <> :args ({ EXPR :typeOid 16 :opType op :op
er { OPER :opno 1054 :opid 1048 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 20 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold 1
:varoattno 20} { CONST :consttype 1042 :constlen -1 :constbyval false
:constisnull false :constvalue 5 [ 5 0 0 0 80 ] })} { EXPR :typeOid 16
:opType op :oper { OPER :opno 1054 :opid 1048 :opresulttype 16 } :args ({
VAR :varno 1 :varattno 20 :vartype 1042 :vartypmod 5 :varlevelsup 0
:varnoold 1 :varoattno 20} { CONST :consttype 1042 :constlen -1
:constbyval false :constisnull false :constvalue 5 [ 5 0 0 0 77 ] })})})
:lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 :indxid ( 2591699101) :indxqual (({ EXPR :typeOid 16 :opType
op :oper { OPER :opno 1325 :opid 2056 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 1 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 8} { CONST :consttype 1184 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ 0 0 0 -64 125 65 -105 65 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 1323 :opid 2055 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8} { CONST :consttype 1184 :constlen
8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 -68 -59 -128
-105 65 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1325 :opid 2056 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8} { CONST :consttype 1184 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ 0 0 0 -64 125 65 -105 65 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 1323 :opid 2055 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8} { CONST :consttype 1184 :constlen
8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 -68 -59 -128
-105 65 ] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :keycount 1 } :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :numCols 1 :tuplePerGroup true } :righttree <>
:extprm () :locprm () :initplan <> :nprm 0 }
NOTICE: QUERY PLAN:
Aggregate (cost=9265841.46..9265841.63 rows=2 width=12) (actual
time=340838.88..340841.70 rows=7 loops=1)
-> Group (cost=9265841.46..9265841.52 rows=24 width=12) (actual
time=340838.83..340840.75 rows=417 loops=1)
-> Sort (cost=9265841.46..9265841.46 rows=24 width=12) (actual
time=340838.81..340839.19 rows=417 loops=1)
-> Index Scan using mo_200302_calling_idx on
mobileorig_200302 (cost=0.00..9265840.92 rows=24 width=12) (actual
time=4156.92..340836.03 rows=417 loops=1)
Total runtime: 340842.06 msec
EXPLAIN
Running a slightly modified query i.e. removing the in and using an
equality, the planner uses the "correct" index this time. Here are the
results.
CDR=# explain analyze verbose select answertime::date, count(*),
sum(callduration) from mobileorig_200302 where answertime between
'2003/2/3 00:00:00' and '2003/2/14 23:59:59' and dialleddigits_value =
'50043992' and callednumber_type = 'P' group by answertime::date;
NOTICE: QUERY DUMP:
{ AGG :startup_cost 121007.20 :total_cost 121007.22 :rows 1 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1082
:restypmod -1 :resname answertime :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 0 :varattno 3 :vartype 1082 :vartypmod
-1 :varlevelsup 0 :varnoold 0 :varoattno 0}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 20 :restypmod -1 :resname count :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname
count :basetype 0 :aggtype 20 :target { CONST :consttype 23 :constlen 4
:constbyval true :constisnull false :constvalue 4 [ 1 0 0 0 ] } :aggstar
true :aggdistinct false }} { TARGETENTRY :resdom { RESDOM :resno 3
:restype 20 :restypmod -1 :resname sum :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { AGGREG :aggname sum :basetype
23 :aggtype 20 :target { VAR :varno 0 :varattno 2 :vartype 23 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 17} :aggstar false :aggdistinct
false }}) :qpqual <> :lefttree { GRP :startup_cost 121007.20 :total_cost
121007.21 :rows 2 :width 12 :qptargetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 1184 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 0 :varattno 1
:vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 0 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 17}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1082
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 0 :varattno 3 :vartype 1082 :vartypmod
-1 :varlevelsup 0 :varnoold 0 :varoattno 0}}) :qpqual <> :lefttree { SORT
:startup_cost 121007.20 :total_cost 121007.20 :rows 2 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 17
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 17}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1082 :restypmod -1 :resname
<> :reskey 1 :reskeyop 1095 :ressortgroupref 0 :resjunk false } :expr {
EXPR :typeOid 1082 :opType func :oper { FUNC :funcid 1178 :functype 1082
} :args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8})}}) :qpqual <> :lefttree {
INDEXSCAN :startup_cost 0.00 :total_cost 121007.19 :rows 2 :width 12
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 23 :restypmod -1 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 17
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 17}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 1082 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { EXPR
:typeOid 1082 :opType func :oper { FUNC :funcid 1178 :functype 1082 }
:args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 8})}}) :qpqual ({ EXPR :typeOid 16 :opType op
:oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno
1 :varattno 13 :vartype 1043 :vartypmod 35 :varlevelsup 0 :varnoold 1
:varoattno 13} { CONST :consttype 1043 :constlen -1 :constbyval false
:constisnull false :constvalue 12 [ 12 0 0 0 53 48 48 52 51 57 57 50 ]
})}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0
:scanrelid 1 :indxi
d ( 2591699100) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER
:opno 1325 :opid 2056 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8} {
CONST :consttype 1184 :constlen 8 :constbyval false :constisnull false
:constvalue 8 [ 0 0 0 -64 125 65 -105 65 ] })} { EXPR :typeOid 16 :opType
op :oper { OPER :opno 1323 :opid 2055 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 1 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 8} { CONST :consttype 1184 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ 0 0 0 -68 -59 -128 -105 65 ] })} {
EXPR :typeOid 16 :opType op :oper { OPER :opno 1054 :opid 1048
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 2 :vartype 1042
:vartypmod 5 :varlevelsup 0 :varnoold 1 :varoattno 20} { CONST :consttype
1042 :constlen -1 :constbyval false :constisnull false :constvalue 5 [ 5
0 0 0 80 ] })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper {
OPER :opno 1325 :opid 2056 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 8 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 8} { CONST :consttype 1184 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ 0 0 0 -64 125 65 -105 65 ] })} { EXPR
:typeOid 16 :opType op :oper { OPER :opno 1323 :opid 2055 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8} { CONST :consttype 1184 :constlen
8 :constbyval false :constisnull false :constvalue 8 [ 0 0 0 -68 -59 -128
-105 65 ] })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1054 :opid
1048 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 20 :vartype 1042
:vartypmod 5 :varlevelsup 0 :varnoold 1 :varoattno 20} { CONST :consttype
1042 :constlen -1 :constbyval false :constisnull false :constvalue 5 [ 5
0 0 0 80 ] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :keycount 1 } :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :numCols 1 :tuplePerGroup true } :righttree <>
:extprm () :locprm () :initplan <> :nprm 0 }
NOTICE: QUERY PLAN:
Aggregate (cost=121007.20..121007.22 rows=1 width=12) (actual
time=10295.75..10298.57 rows=7 loops=1)
-> Group (cost=121007.20..121007.21 rows=2 width=12) (actual
time=10295.71..10297.63 rows=417 loops=1)
-> Sort (cost=121007.20..121007.20 rows=2 width=12) (actual
time=10295.69..10296.08 rows=417 loops=1)
-> Index Scan using mo_200302_called_idx on
mobileorig_200302 (cost=0.00..121007.19 rows=2 width=12) (actual
time=250.15..10293.56 rows=417 loops=1)
Total runtime: 10298.86 msec
EXPLAIN
Is this what you wanted or am I misunderstanding you ?
Regards
Robert
Tom Lane <tgl@sss.pgh.pa.us>
22/05/2003 17:51
To: Robert.Farrugia@go.com.mt
cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] After VACUUM, statistics become skewed
Robert.Farrugia@go.com.mt writes:
> Here are the results.
I asked for EXPLAIN ANALYZE for both cases. When you're worried about
planner estimates diverging from reality, it does not help to not know
what reality is.
regards, tom lane
view thread (8+ messages) latest in thread
Message-ID: <20030523093733.0F9AC924DF1@developer.postgresql.org>
Permalink: ../20030523093733.0F9AC924DF1@developer.postgresql.org/
Also on: postgresql.org/message-id/20030523093733.0F9AC924DF1@developer.postgresql.org
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Robert.Farrugia@go.com.mt, tgl@sss.pgh.pa.us
Subject: Re: After VACUUM, statistics become skewed
In-Reply-To: <20030523093733.0F9AC924DF1@developer.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox