X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 53C11D1B19F for ; Thu, 8 Jul 2004 06:07:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17270-10 for ; Thu, 8 Jul 2004 09:07:41 +0000 (GMT) Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40]) by svr1.postgresql.org (Postfix) with ESMTP id 71E28D1B18F for ; Thu, 8 Jul 2004 06:07:39 -0300 (ADT) Received: from gateway.pspl.co.in ([203.199.147.2]:3144 helo=frodo.hserus.net) by frodo.hserus.net with asmtp (Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.34 #0) id 1BiUsW-000LRj-Ny by authid with plain; Thu, 08 Jul 2004 14:37:41 +0530 Message-ID: <40ED0ED9.9000106@frodo.hserus.net> Date: Thu, 08 Jul 2004 14:37:37 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Bill Chandler Cc: pgsql-performance@postgresql.org Subject: Re: Terrible performance after deleting/recreating indexes References: <20040707161640.48270.qmail@web51408.mail.yahoo.com> In-Reply-To: <20040707161640.48270.qmail@web51408.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/33 X-Sequence-Number: 7424 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