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

Sorry, I missed your bottom part before I replied last.

The table breakdown consists of about 8000 symbol_names with at most
5000 rows of data for each symbol ( stock market history ).

So, those sample percents seem huge. The most any symbol would have
would be about 5000 / (8000*5000) = .0125%

--Michael

Tom Lane wrote:

>"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
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>