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

Here's the new stats since the vacuum on that column--quite a few changes.

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 | 150712 |
{EBALX,ELTE,SRP,KMG,MKC,AEN,BAC,BDX,BKF,BRT} |
{0.0233333,0.018,0.00266667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
| {A,BRN,DPAC,FUTR,INTC,MDSN,OCA,RAA,SSYS,USTB,_^^VPM} | 0.112971
(1 row)

I'll alter and play with the table tomorrow and let you know what I find.

-Michael

Tom Lane wrote:

>"Michael G. Martin" <michael(at)vpmonline(dot)com> writes:
>
>>I just ran a vacuum analyze with the specific column. Still get the
>>same explain plan:
>>
>
>Did the pg_stats data change noticeably?
>
>ANALYZE is a statistical sampling process in 7.2, so I'd expect the
>results to move around somewhat each time you repeat it. But if it
>changes a lot then we have a problem.
>
>You could also try
>
>ALTER TABLE symbol_data ALTER symbol_name SET STATISTICS n
>
>for larger values of n (10 is the default) and then re-ANALYZE
>to see if the stats get any more accurate. The default of 10
>was more or less picked out of the air ... perhaps it's too small.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>