I had an issue where my index was not always used on a very large table.
The issue came down to the data distribution and not pulling in enough
of a random sample to get an accurate estimate ( I think the default max
value was around 3000 sample rows ( 300 * 10 default_samples -- see
analyze.c ) rows. I fixed the issue by following Tom's advice and
increased the statistics count on my table to pull in 300000 rows (1000
samples *300). I had to play with the value, re-analyze, and check the
stats in the pg_stats table until most_common_freqs on some values were
all fairily close. The explain plan still shows me a cost and row value
way above what is physically in the table, but at least my indexes were
being used.
alter table table_name alter symbol_name set statistics 1000;
--Michael
Tom Lane wrote:
>Ron Mayer <ron@intervideo.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
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>