Re: Index used under 7.2 slows down query?

On Tue, 2002-07-09 at 10:59, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> > We're trying to track down a rather large performance difference between
> > 7.2 and 7.1.  Statistics gathered show that one query takes a lot longer
> > under 7.2 than under 7.1.
>
> Could we see EXPLAIN ANALYZE output from 7.2?  Also, if you force
> enable_indexscan OFF, what does EXPLAIN ANALYZE say then?
>

Unfortunately, no.  I've whacked the indexes that were in use on the 7.2
schema, pursuing the idea that maybe without the indexes it would run
faster.  Certainly the EXPLAIN numbers point toward that.

What I think was happening: under 7.1, the table was too small (perhaps
at the time it was vacuumed) to "qualify" for index access.  Under 7.2,
perhaps because of more numerous vacuums to solve a similar problem, the
index was deemed "good enough".  However, since this table has about 1k
new rows per hour, all with similar values for the index, and likewise
1k deletes per hour, the index distribution is off almost immediately
after the vacuum!

I'm going to run this for a day without any indexes (except for the
primary key, which is a sequence number).  Then I may try it again with
indexes and a vacuum/analyze every 10-15 minutes.


--
Jeff Boes                                      vox 616.226.9550 ext 24
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise