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
