Re: After VACUUM, statistics become skewed - Mailing list pgsql-admin
| From | Robert.Farrugia@go.com.mt |
|---|---|
| Subject | Re: After VACUUM, statistics become skewed |
| Date | |
| Msg-id | 20030523093733.0F9AC924DF1@developer.postgresql.org Whole thread Raw |
| In response to | After VACUUM, statistics become skewed (Robert.Farrugia@go.com.mt) |
| Responses |
Re: After VACUUM, statistics become skewed
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-admin |
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 :v arnoold 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 :cons tlen 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 9265 841.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 92 65840.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 :v arnoold 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 :c onstvalue 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 :constva lue 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
pgsql-admin by date: