Date: Sat, 11 Feb 2006 15:24:53 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Aaron Turner <synfinatic@gmail.com>
Cc: "Matthew T. O'Connor" <matthew@zeut.net>, pgsql-performance@postgresql.org
Subject: Re: 10+hrs vs 15min because of just one index
Message-ID: <20060211212453.GT57845@pervasive.com>
References: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com>
	<43ECC9BF.2030107@zeut.net>
	<1ca1c1410602100924s191e7cddo4bda80d8f8d7cb6d@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1ca1c1410602100924s191e7cddo4bda80d8f8d7cb6d@mail.gmail.com>
User-Agent: Mutt/1.5.11

On Fri, Feb 10, 2006 at 09:24:39AM -0800, Aaron Turner wrote:
> On 2/10/06, Matthew T. O'Connor <matthew@zeut.net> wrote:
> > Aaron Turner wrote:
> > > So I'm trying to figure out how to optimize my PG install (8.0.3) to
> > > get better performance without dropping one of my indexes.
> >
> > What about something like this:
> >
> > begin;
> > drop slow_index_name;
> > update;
> > create index slow_index_name;
> > commit;
> > vacuum;
> 
> Right.  That's exactly what I'm doing to get the update to occur in 15
> minutes.  Unfortunately though, I'm basically at the point of every
> time I insert/update into that table I have to drop the index which is
> making my life very painful (having to de-dupe records in RAM in my
> application is a lot faster but also more complicated/error prone).
> 
> Basically, I need some way to optimize PG so that I don't have to drop
> that index every time.
> 
> Suggestions?

I think you'll have a tough time making this faster; or I'm just not
understanding the problem well enough. It's probably time to start
thinking about re-architecting some things in the application so that
you don't have to do this.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461