Re: Inconsistant use of index.

Ron Mayer <ron(at)intervideo(dot)com> writes:
> Once some of my tables started getting pretty large, PostgreSQL
> suddenly stopped using indexes when I use expressions like "col = value"
> decreasing performance by 20X.

Hmm. The EXPLAIN shows that the planner is not doing too badly at
estimating the number of rows involved:

> logs2=# explain analyze select count(*) from fact where dat='2002-03-01';
> NOTICE: QUERY PLAN:

> Aggregate (cost=375631.14..375631.14 rows=1 width=0) (actual
> time=76689.42..76689.42 rows=1 loops=1)
> -> Seq Scan on fact (cost=0.00..375101.72 rows=211765 width=0) (actual
> time=20330.96..76391.94 rows=180295 loops=1)
> Total runtime: 76707.92 msec

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?

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?)

regards, tom lane