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-02-28 04:32:53
Message-ID: 3C7DB2F5.1000901@vpmonline.com (view raw or flat)
Thread:
Lists: pgsql-bugs
yes.  each symbol_name only gets one row added and maybe a few updated 
each market day.
This is interesting too.  Planner thinks 128 rows on this symbol, GE, 
yet there are really 5595.  Not as off as ELTE, but a large factor.  at 
least the index get hit here.

explain select * from symbol_data where symbol_name='GE';
NOTICE:  QUERY PLAN:

Index Scan using symbol_data_pkey on symbol_data  (cost=0.00..513.09 
rows=128 width=129)

EXPLAIN
vpm=> select count(*) from  symbol_data where symbol_name='GE';
 count
-------
  5595



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
>

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-02-28 04:40:07
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Previous:From: Tom LaneDate: 2002-02-28 04:28:19
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze

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