pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / 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: Thu, 22 May 2003 09:57:30 +0200
Message-ID: <20030522075734.C53F29234B3@developer.postgresql.org> (raw)
Hi Tom,
I delete the rows from the pg_statistics table and rerun the same query;
Here are the results.
NOTICE: QUERY PLAN:
Aggregate (cost=218105.86..218105.88 rows=1 width=12)
-> Group (cost=218105.86..218105.87 rows=3 width=12)
-> Sort (cost=218105.86..218105.86 rows=3 width=12)
-> Index Scan using mo_200302_calling_idx on
mobileorig_200302 (cost=0.00..218105.84 rows=3 width=12)
EXPLAIN
As you can see it is still using the wrong index but the cost is less.
I am attaching the output of the pg_stats table for the table shown in the
example.
Thanks
Robert
Tom Lane <tgl@sss.pgh.pa.us>
21/05/2003 18: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:
> Given a table around 4GB in size containing millions of records, before
> vacuuming the following query used the correct index
> (mo_200302_called_idx) which is built on answertime and
callednumber_type.
Could we see EXPLAIN ANALYZE results for both the good plan and the bad
plan, for the same query? You're not giving us comparable results.
It would also be useful to show the pg_stats entries for the table.
I suspect that the planner is being fooled by some odd statistics of
your data, but we have not got any info here with which to speculate.
> Usually by dropping the table and reloading it from
> disk may solve the problem (it may take more than one try), but this is
> becoming unpractical due to the amount of data to restore each time.
You can get back to the un-analyzed state by deleting the rows in
pg_statistic for this table, viz
delete from pg_statistic where starelid = (select oid
from
pg_class where relname =
'mobileorig_200302');
Grotty, but it sure beats dropping and reloading a big table.
regards, tom lane
Attachments:
stats.txt (text/plain, 17.5K) download
tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
-------------------+-----------------------+-----------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
mobileorig_200302 | pkey | 0 | 4 | -1 | | | {395752603,397106244,398550786,399921134,401251679,402551709,403730553,404869360,406153085,407365147,408553241} | 0.930411
mobileorig_200302 | callforwardindicator | 0 | 1 | 2 | {f,t} | {0.948333,0.0516667} | | 0.903291
mobileorig_200302 | callingparty_value | 0 | 18 | 40762 | {278210300018865,278210300042555,278210300097337,278210300123965,278210300211941,278210300243955,0,278210300000038,278210300000098,278210300002671} | {0.001,0.001,0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667} | {206102100730712,278210300015228,278210300031607,278210300045716,278210300065465,278210300081090,278210300099056,278210300113514,278210300127763,278210300213499,425010500803478} | -0.0117581
mobileorig_200302 | callingnumber_value | 0 | 12 | 39982 | {79007900,79257977,79277518,79298302,79451169,79709866,0,79014642,79047265,79048154} | {0.001,0.001,0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667} | {21344677,79058264,79200983,79254300,79270901,79288610,79418805,79492421,79604208,79710621,99478710} | 0.0131976
mobileorig_200302 | callednumber_value | 0 | 12 | 12468 | {125,79222121,123,121,0,146,195,196,112,21244089} | {0.0553333,0.0466667,0.029,0.017,0.0133333,0.00266667,0.002,0.002,0.001,0.001} | {0021262255854,21465178,79051663,79257809,79405506,79667123,797000070007036,99221525,99452594,99802332,99898818} | 0.0102895
mobileorig_200302 | additionalinformation | 0 | 2 | 2 | {-1,1} | {0.999,0.001} | | 0.995143
mobileorig_200302 | channelallocationtime | 0 | 8 | -0.121449 | {"2003-02-13 11:46:54+01","2003-02-16 12:22:43+01","2003-02-28 14:23:56+01"} | {0.000666667,0.000666667,0.000666667} | {"2003-02-01 00:02:03+01","2003-02-04 00:20:01+01","2003-02-06 22:17:37+01","2003-02-09 18:40:01+01","2003-02-12 18:26:41+01","2003-02-15 15:07:28+01","2003-02-18 12:09:13+01","2003-02-20 19:37:10+01","2003-02-23 15:14:00+01","2003-02-26 13:08:59+01","2003-02-28 23:54:15+01"} | 0.930411
mobileorig_200302 | answertime | 0 | 8 | 3310 | {"1970-01-01 01:00:00+01","2003-02-13 11:46:54+01","2003-02-15 22:02:20+01","2003-02-20 17:59:22+01"} | {0.474,0.000666667,0.000666667,0.000666667} | {"2003-02-01 08:44:48+01","2003-02-04 10:07:13+01","2003-02-07 09:39:03+01","2003-02-10 08:26:32+01","2003-02-13 08:50:01+01","2003-02-15 18:48:35+01","2003-02-18 17:46:47+01","2003-02-21 10:13:06+01","2003-02-23 18:02:55+01","2003-02-26 16:30:15+01","2003-02-28 23:54:23+01"} | 0.504523
mobileorig_200302 | disconnecttime | 0 | 8 | 35881 | {"1970-01-01 01:00:00+01","2003-02-19 15:03:18+01","2003-02-23 16:49:54+01"} | {0.0756667,0.000666667,0.000666667} | {"2003-02-01 00:02:33+01","2003-02-03 20:57:05+01","2003-02-06 20:00:31+01","2003-02-09 18:25:55+01","2003-02-12 18:12:19+01","2003-02-15 14:06:29+01","2003-02-18 12:03:26+01","2003-02-20 19:36:23+01","2003-02-23 14:49:07+01","2003-02-26 12:45:19+01","2003-02-28 23:37:44+01"} | 0.798649
mobileorig_200302 | releasetime | 0 | 8 | -0.171792 | {"2003-02-19 15:03:18+01","2003-02-23 16:49:54+01"} | {0.000666667,0.000666667} | {"1970-01-01 01:00:00+01","2003-02-03 23:05:27+01","2003-02-06 22:17:41+01","2003-02-09 18:40:21+01","2003-02-12 18:26:46+01","2003-02-15 15:00:45+01","2003-02-18 12:03:26+01","2003-02-20 19:36:23+01","2003-02-23 15:14:26+01","2003-02-26 13:28:42+01","2003-02-28 23:55:28+01"} | 0.929869
mobileorig_200302 | causefortermination | 0 | 2 | 4 | {0,3} | {0.890333,0.102333} | {1,4} | 0.801498
mobileorig_200302 | callreference | 0 | 4 | 12551 | {770,1350,1819,3418,5199,5328,5380,6427,6554,7838} | {0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001} | {0,1365,2729,3984,5258,6558,7844,9015,10330,11495,12614} | -0.0153471
mobileorig_200302 | dialleddigits_value | 0 | 11 | 11321 | {1232,0035679222121,123,121,1231,146,195,196,A,112} | {0.066,0.0473333,0.0433333,0.0193333,0.00266667,0.002,0.002,0.002,0.00133333,0.001} | {000A,00819032734152,21552480,79051405,79252680,79292603,79594028,99226816,99454110,99805351,B13B79054071} | 0.0109973
mobileorig_200302 | outgoingtrunkgroup | 0 | 2 | 16 | {-1,184,156} | {0.330333,0.274,0.108} | {93,94,95,95,105,114,155,157,159,159,186} | 0.226706
mobileorig_200302 | outgoingtrunkmember | 0 | 2 | 156 | {-1,1,6,2,5,13,3,7,17,12} | {0.330333,0.0153333,0.0143333,0.0133333,0.013,0.013,0.0126667,0.0126667,0.012,0.0113333} | {4,14,22,29,44,60,79,100,128,146,159} | 0.115936
mobileorig_200302 | outgoingroutegroup | 0 | 2 | 7 | {-1,8,2,10,0,6,4} | {0.498667,0.274,0.108,0.0673333,0.0263333,0.021,0.00466667} | | 0.3567
mobileorig_200302 | callduration | 0 | 4 | 194 | {0,6,1,7,9,8,2,5,11,10} | {0.474,0.0283333,0.026,0.0216667,0.0186667,0.0183333,0.018,0.018,0.016,0.015} | {3,13,16,20,26,32,42,54,73,105,929} | 0.258961
mobileorig_200302 | recordnumber | 0 | 4 | -0.293282 | {7117463} | {0.000666667} | {3740,982823,2027048,2981985,3882458,4907895,5939979,6887300,7943059,8943344,9992895} | -0.0854432
mobileorig_200302 | callingnumber_type | 0 | 5 | 5 | {G,N,M,O,V} | {0.984667,0.0133333,0.000666667,0.000666667,0.000666667} | | 0.969119
mobileorig_200302 | callednumber_type | 0 | 5 | 13 | {G,V,M,I} | {0.353333,0.278333,0.128,0.105667} | {C,L,L,L,L,N,O,S,W} | 0.260884
mobileorig_200302 | realcall | 0 | 1 | 2 | {t,f} | {0.997,0.003} | | 0.996456
mobileorig_200302 | callingequipment | 0 | 18 | 23195 | {"",350103919544660,350103102621260,350104600969360,350104800332950,350861270206360,332064532254510,332152352899330,332167671926980,332168677871870} | {0.057,0.002,0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667} | {004999010640000,350019513541970,350103919436280,350150801914790,350608806911740,350721893055880,350843805206090,350987108151770,448896606233090,449269404349480,520413620002610} | -0.0185836
mobileorig_200302 | diagnostic_protocol | 0 | 2 | 3 | {0,2,-1} | {0.831333,0.156,0.0126667} | | 0.715434
mobileorig_200302 | diagnostic_errorcode | 0 | 2 | 19 | {16} | {0.875333} | {-1,-1,17,17,17,18,27,34,41,47,127} | 0.760368
mobileorig_200302 | ss_services | 0 | 7 | 12 | {"12,"} | {0.936} | {"","12,42,","21,","29,","2a,","2a,","2b,","2b,","2b,","2b,","f9,"} | 0.885679
mobileorig_200302 | vsd_type | 0 | 5 | 3 | {" ",E,V} | {0.997333,0.002,0.000666667} | | 0.998377
mobileorig_200302 | use_for_statistics | 0 | 1 | 2 | {t,f} | {0.998,0.002} | | 0.992622
(27 rows)
view thread (8+ messages) latest in thread
Message-ID: <20030522075734.C53F29234B3@developer.postgresql.org> Permalink: ../20030522075734.C53F29234B3@developer.postgresql.org/ Also on: postgresql.org/message-id/20030522075734.C53F29234B3@developer.postgresql.org ·
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: <20030522075734.C53F29234B3@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