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

Good news.

I looked through the code and after a little debugging found that the
STATISTICS * 300 gives you the sample size of rows used to gather
statistics.
With the symbol_data table with 20million tuples and on this column with
about 8000 unique values, i needed a very large sample size.
Even with a STATISTICS of 500 ( 150,000  random rows) I still got a few
symbols with a most_common_freqs of .01 or so.
Bumping the STATISTICS to 1000 put the highest most_common_freqs at
0.00788667, so no seq scans now.
Not too much of a time difference in the analyze either--at least not an
impact.


The only strange thing I see is still the estimated rows returned.  The
index is picked, so I don't know that it matters.  Even though this
query has 688 tuples, the explain thinks 17k+:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..70648.22
rows=17700 width=129)

-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
>>
>