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: Re: Terrible performance after deleting/recreating indexes
Date: Thu, 8 Jul 2004 13:49:21 -0700 (PDT)
Message-ID: <20040708204921.4921.qmail@web51407.mail.yahoo.com> (raw)
In-Reply-To: <40ED0ED9.9000106@frodo.hserus.net>

Thanks for the advice.

On further review it appears I am only getting this 
performance degradation when I run the command via
a JDBC app.  If I do the exact same query from
psql, the performance is fine.  I've tried both the
JDBC2 and JDBC3 jars.  Same results.

It definitely seems to correspond to deleting and
recreating the indexes, though.  The same query thru
JDBC worked fine before recreating the indexes.  

Does that make any sense at all?

thanks

Bill

--- Shridhar Daithankar <shridhar@frodo.hserus.net>
wrote:
> Bill Chandler wrote:
> 
> > 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.  
> 
> Get an explain analyze. That gives actual v/s
> planned time spent. See what is 
> causing the difference. A discrepency between
> planned and actual row is usually 
> a indication of out-of-date stats.
> 
> 
> Which are the indexes on these tables? You should
> list fields with indexes first 
> in where clause. Also list most selective field
> first so that it eliminates as 
> many rows as possible in first scan.
> 
> 
> I hope you have read the tuning articles on
> varlena.com and applied some basic 
> tuning.
> 
> And post the table schema, hardware config,
> postgresql config(important ones of 
> course) and  explain analyze for queries. That would
> be something to start with.
> 
>   Shridhar
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> 



		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 



view thread (5+ messages)  latest in thread

Message-ID: <20040708204921.4921.qmail@web51407.mail.yahoo.com>
Permalink:  ../20040708204921.4921.qmail@web51407.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040708204921.4921.qmail@web51407.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: <20040708204921.4921.qmail@web51407.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