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

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
>