pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Chandler <billybobc1210@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Terrible performance after deleting/recreating indexes
Date: Wed, 7 Jul 2004 09:16:40 -0700 (PDT)
Message-ID: <20040707161640.48270.qmail@web51408.mail.yahoo.com> (raw)

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 



view thread (5+ messages)  latest in thread

Message-ID: <20040707161640.48270.qmail@web51408.mail.yahoo.com>
Permalink:  ../20040707161640.48270.qmail@web51408.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040707161640.48270.qmail@web51408.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: billybobc1210@yahoo.com
  Subject: Re: Terrible performance after deleting/recreating indexes
  In-Reply-To: <20040707161640.48270.qmail@web51408.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox