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

Terrible performance after deleting/recreating indexes

From: Bill Chandler <billybobc1210(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Terrible performance after deleting/recreating indexes
Date: 2004-07-07 16:16:40
Message-ID: 20040707161640.48270.qmail@web51408.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
Hi,

Using PostgreSQL 7.4.2 on Solaris.  I'm trying to 
improve performance on some queries to my databases so
I wanted to try out various index structures.  

Since I'm going to be running my performance tests
repeatedly, I created some SQL scripts to delete and 
recreate various index configurations.  One of the
scripts contains the commands for recreating the 
'original' index configuration (i.e. the one I've 
already got some numbers for).  Only thing is now
when I delete and recreate the original indexes then
run the query, I'm finding the performance has gone
completely down the tubes compared to what I 
originally had.  A query that used to take 5 minutes
to complete now takes hours to complete.

For what it's worth my query looks something like:

select * from tbl_1, tbl_2 where tbl_1.id = tbl_2.id
and tbl_2.name like 'x%y%' and tbl_1.x > 1234567890123
order by tbl_1.x;

tbl_1 is very big (> 2 million rows)
tbl_2 is relatively small (7000 or so rows)
tbl_1.x is a numeric(13)
tbl_1.id & tbl_2.id are integers
tbl_2.name is a varchar(64)

I've run 'VACUUM ANALYZE' on both tables involved in
the query.  I also used 'EXPLAIN' and observed that
the query plan is completely changed from what it 
was originally.  

Any idea why this would be?  I would have thougth 
that a freshly created index would have better 
performance not worse.  I have not done any inserts
or updates since recreating the indexes.

thanks in advance,

Bill C

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Responses

pgsql-performance by date

Next:From: Missner, T. R.Date: 2004-07-07 17:24:11
Subject: inserting into brand new database faster than old database
Previous:From: Ioannis TheoharisDate: 2004-07-07 15:13:42
Subject: Implementatiion of Inheritance in Postgres

pgsql-jdbc by date

Next:From: Dave CramerDate: 2004-07-07 19:53:50
Subject: Re: getXXX methods
Previous:From: Dario V. FassiDate: 2004-07-07 06:31:25
Subject: ResultSerMetaData.getColumnDisplaySize() with timestamp error

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