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:
[EMAIL PROTECTED]">"Michael G. Martin" <[EMAIL PROTECTED]> 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
