Re: Indexes not always used after inserts/updates/vacuum analyze

"Michael G. Martin" <michael(at)vpmonline(dot)com> writes:
> Here is what is actually there:

> select count(*) from symbol_data where symbol_name='ELTE';
> 687

Hmm. Do you have reason to think that that was also true when you last
did VACUUM ANALYZE or VACUUM?

> Here is the pg_stat query:
> select * from pg_stats where tablename = 'symbol_data' and attname
> ='symbol_name';
> tablename | attname | null_frac | avg_width | n_distinct
> | most_common_vals
> |
> most_common_freqs
> | histogram_bounds | correlation
> -------------+-------------+-----------+-----------+------------+----------------------------------------------+---------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-------------
> symbol_data | symbol_name | 0 | 7 | 152988 |
> {EBALX,ELTE,LIT,OEX,RESC,BS,ESH,HOC,IBC,IDA} |
> {0.0183333,0.0173333,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
> | {A,BMO,DBD,FSCHX,IIX,MAS,NSANY,PTEC,SR,UTIL,_^^VPM} | 0.128921
> (1 row)

What this says is that in the last ANALYZE, EBALX accounted for 18% of
the sample, and ELTE for 17%. Does that seem plausible to you? If the
sample was accurate then I'd agree with the planner's choices. It'd
seem that either your table contents are changing drastically (in which
case more-frequent ANALYZEs may be the answer), or you had the bad luck
to get a very unrepresentative sample, or there's some bug in the
statistical calculations.

regards, tom lane