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

["Michael G. Martin" <michael(at)vpmonline(dot)com>]

Ok, so this morning after the automated nightly vacuum -z -v on the 
database, ELTE no longer appears in the pg_stats table, and the index is 
picked no problem.  The table data has not changed since last eve.

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:

> "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