Re: After VACUUM, statistics become skewed

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(at)sss(dot)pgh(dot)pa(dot)us>
22/05/2003 17:51

To: Robert(dot)Farrugia(at)go(dot)com(dot)mt
cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] After VACUUM, statistics become skewed

Robert(dot)Farrugia(at)go(dot)com(dot)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