X-Original-To: pgsql-admin@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 98C7E925113 for ; Fri, 23 May 2003 07:19:56 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 22481-10 for ; Fri, 23 May 2003 07:19:23 -0400 (EDT) Received: from Europa.go.com.mt (unknown [194.158.48.18]) by developer.postgresql.org (Postfix) with ESMTP id 0F9AC924DF1 for ; Fri, 23 May 2003 05:37:32 -0400 (EDT) To: tgl@sss.pgh.pa.us Cc: pgsql-admin@postgresql.org Subject: Re: After VACUUM, statistics become skewed MIME-Version: 1.0 From: Robert.Farrugia@go.com.mt Date: Fri, 23 May 2003 11:37:30 +0200 Content-Type: multipart/alternative; boundary="=_alternative 0034D2BBC1256D2F_=" Message-Id: <20030523093733.0F9AC924DF1@developer.postgresql.org> X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/348 X-Sequence-Number: 8910 This is a multipart message in MIME format. --=_alternative 0034D2BBC1256D2F_= Content-Type: text/plain; charset="us-ascii" 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 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 --=_alternative 0034D2BBC1256D2F_= Content-Type: text/html; charset="us-ascii"
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

--=_alternative 0034D2BBC1256D2F_=--