Skip site navigation (1) Skip section navigation (2)

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

From: "Michael G(dot) Martin" <michael(at)vpmonline(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Date: 2002-02-28 14:40:15
Message-ID: 3C7E414F.7020406@vpmonline.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-02-28 14:51:06
Subject: Re: Indexes not always used after inserts/updates/vacuum analyze
Previous:From: Reinhard MaxDate: 2002-02-28 11:56:09
Subject: Re: Indexes not always used after inserts/updates/vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group