However, now there is a new symbol which is behaving the same way--I. This symbol was just loaded into the database yesterday. There are officially 4108 rows in the symbol_data table where symbol_name='I'. I bumped the STATISTICS value up to 100, re-analyzed, but the pg_stats table still shows I first on the list with a value of 0.0182--didn't change much from the original STATISTICS value of 10.
Here are the explain analyzes:
set enable_seqscan = on;
explain analyze select * from symbol_data where symbol_name='I' order by date;
NOTICE: QUERY PLAN:
Sort (cost=811813.33..811813.33 rows=373904 width=129) (actual time=93423.45..93427.02 rows=4108 loops=1)
-> Seq Scan on symbol_data (cost=0.00..709994.20 rows=373904 width=129) (actual time=92483.55..93399.60 rows=4108 loops=1)
Total runtime: 93431.50 msec
set enable_seqscan = off;
SET VARIABLE
vpm=> explain analyze select * from symbol_data where symbol_name='I' order by date;
NOTICE: QUERY PLAN:
Sort (cost=1584564.49..1584564.49 rows=373904 width=129) (actual time=129.38..133.01 rows=4108 loops=1)
-> Index Scan using symbol_data_pkey on symbol_data (cost=0.00..1482745.36 rows=373904 width=129) (actual time=21.54..105.46 rows=4108 loops=1)
Total runtime: 137.55 msec
Even though the optimizer thinks the index will cost more, it does pick it and use it with the performance expected when enable_seqscan = off;
-Michael
Tom Lane wrote:
[EMAIL PROTECTED]">"Michael G. Martin" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
