On Tue, 26 Mar 2002, Tom Lane wrote: > > Ron Mayer <[EMAIL PROTECTED]> writes: > > [...] pretty large, PostgreSQL suddenly stopped using indexes [...] > [...] > > 212K estimate for 180K real is not bad at all. So the problem is in the > cost models not the initial row count estimation. > > If you force an indexscan via "set enable_seqscan to off", what does > EXPLAIN ANALYZE report?
It then uses the index: =================================================================== == logs2=# set enable_seqscan to off; == SET VARIABLE == logs2=# explain analyze select count(*) from fact where dat='2002-03-01'; == == NOTICE: QUERY PLAN: == == Aggregate (cost=840488.03..840488.03 rows=1 width=0) (actual == time=2753.82..2753.82 rows=1 loops=1) == -> Index Scan using i_fact__dat on fact (cost=0.00..839957.59 rows=212174 == width=0) (actual time=101.25..2434.00 rows=180295 loops=1) == Total runtime: 2754.24 msec =================================================================== > Also, what do you get from > select * from pg_stats where tablename = 'fact'; > I'm particularly interested in the correlation estimate for the dat > column. (Would you happen to have an idea whether the data has been > inserted more-or-less in dat order?) I've attached that output as an attachment. I beleve much of February was loaded first, then we back-filled January, and daily I've been adding March's results. I don't believe the index-usage stopped when we did the january fill... something happend a few days ago after a pretty routine daily load. Oh... one more interesting thing... There are a couple big exceptions to the even distribution of data. Almost every day has between 190000 and 270000 records except '2002-03-08' which has 404293 records and '2002-03-25' which has 6 records. For that particular day, the "<= ... >=" trick doesn't work either. =================================================================== ==logs2=# explain select count(*) from fact where dat<='2002-03-08' and ==dat>='2002-03-08'; ==NOTICE: QUERY PLAN: == ==Aggregate (cost=422125.92..422125.92 rows=1 width=0) == -> Seq Scan on fact (cost=0.00..421128.67 rows=398900 width=0) == ==EXPLAIN ==logs2=# ==logs2=# explain select count(*) from fact where dat<='2002-03-07' and ==dat>='2002-03-07'; ==NOTICE: QUERY PLAN: == ==Aggregate (cost=6.00..6.00 rows=1 width=0) == -> Index Scan using i_fact__dat on fact (cost=0.00..5.99 rows=1 width=0) == ==EXPLAIN =================================================================== I also believe that may have been the day when the index stopped working for "=" for all dates. Ron
tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | \ most_common_freqs | histogram_bounds \ | correlation -----------+---------+-----------+-----------+------------+---------------------------------------------------------------------------------------------+----------------------------\ -----------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------\ ---------------------------------------+------------- fact | dat | 0 | 4 | 83 | {2002-03-08,2002-03-09,2002-01-05,2002-01-18,2002-02-04,2002-03-24,2002-03-23,2002-02-16} | {0.0216667,0.0186667,0.0163\ 333,0.016,0.016,0.016,0.0156667,0.0153333} | {2002-01-01,2002-01-09,2002-01-16,2002-01-25,2002-02-01,2002-02-09,2002-02-17,2002-0\ 2-24,2002-03-05,2002-03-15,2002-03-22} | 0.086088 fact | tim | 0 | 8 | 226609 | {00:30:24,03:07:10,04:04:29,04:17:39,05:08:28,05:14:47,05:15:45,05:20:08,05:36:23,05:59:09} | {0.000666667,0.000666667,0.\ 000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {00:01:35,03:15:29,05:57:26,08:03:40,09:52:10,11:40:56,13:25:21,15:34:16,17:59:13,20\ :35:18,23:58:42} | -0.00532619 fact | ip_id | 0 | 4 | 217853 | {10068,12843,1773838,6047,12844,23567,24900,484794,5637,9246} | {0.01,0.00266667,0.00233333\ ,0.00166667,0.00166667,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333} | {38,20463,91088,236641,430973,634542,842818,1091776,1326722,1566578,1840292} \ | 0.534597 fact | bid_id | 0 | 4 | 232209 | {3464686,26807,200623,940572,1930659,176,304382,538164,875625,1727666} | {0.00233333,0.00133333,0.00\ 1,0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667} | {391,254660,580421,915570,1293992,1622419,1982200,2364036,2759738,3215746,3591914} \ | 0.888364 fact | req_id | 0 | 4 | 60152 | {3,2754,14580,14578,570937,1,1024878,2752,12637,122374} | {0.044,0.0423333,0.0296667,\ 0.0196667,0.0183333,0.013,0.0126667,0.012,0.012,0.0106667} | {22,2384,3645,11143,12729,100772,411864,588108,751221,984277,1389507} \ | 0.263206 fact | ref_id | 0 | 4 | 27840 | {2,3309,4952,4885,2834,4254,4971,4519,3383,4878} | {0.459667,0.0583333,0.051,0\ .0223333,0.0213333,0.0166667,0.0156667,0.0136667,0.0106667,0.0106667} | {91,1899,2827,3395,4896,4947,4970,5730,18347,169935,352748} \ | 0.234155 (6 rows)
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly