Skip site navigation (1) Skip section navigation (2)

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

From: "Michael G(dot) Martin" <michael(at)vpmonline(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Date: 2002-03-01 16:44:09
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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 
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 

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)


> 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

In response to


pgsql-bugs by date

Next:From: Tom LaneDate: 2002-03-01 21:03:43
Subject: Re: SELECT statement causing terminating the backend
Previous:From: Thomas LockhartDate: 2002-03-01 15:21:01
Subject: Re: Bug #605: timestamp(timestamp('a timestamp)) no longer works

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group