Hi Tom,
Here is what is actually there:
select count(*) from symbol_data where symbol_name='ELTE';
count
-------
687
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)
Interesting eh? EBALX also does a full scan--all others in the above list get an index scan.
Here's the variable stuff--I attached the verbose outputs.
set enable_seqscan = on;
SET VARIABLE
vpm=> explain select * from symbol_data where symbol_name='ELTE';
NOTICE: QUERY PLAN:
Seq Scan on symbol_data (cost=0.00..707611.68 rows=355958 width=129)
set enable_seqscan = off;
SET VARIABLE
vpm=> explain select * from symbol_data where symbol_name='ELTE';
NOTICE: QUERY PLAN:
Index Scan using symbol_data_pkey on symbol_data (cost=0.00..1420899.51 rows=355958 width=129)
Index scan appears slower in explain, but the rows value is weird.
Thanks,
Michael
Tom Lane wrote:
"Michael G. Martin" <michael@vpmonline.com> writes:
Here is what I would expect which usually happens:
explain select * from symbol_data where symbol_name='IBM';
Index Scan using symbol_data_pkey on symbol_data (cost=0.00..512.99 rows=128 width=129)
Here is one that fails:
explain select * from symbol_data where symbol_name='ELTE';
Seq Scan on symbol_data (cost=0.00..707415.32 rows=438015 width=129)
The planner thinks that there are 438K occurrences of 'ELTE' in your
table. If that's true, a sequential scan is not obviously the wrong
choice. How many are there, in reality? What do you get from
select * from pg_stats where tablename = 'symbol_data' and attname =
'symbol_name';
Also, to put the rubber to the road: if you force an indexscan by
doing "set enable_seqscan = off", does it get faster or slower?
(EXPLAIN ANALYZE would be useful here.)
regards, tom lane