Ron Mayer <[EMAIL PROTECTED]> 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
---------------------------(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
