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@vpmonline.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@postgresql.org so that your
>message can get through to the mailing list cleanly
>