Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 033F09F9E82
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 27 Aug 2007 11:45:27 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 21874-03-6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 27 Aug 2007 11:45:12 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mx00.pub.collaborativefusion.com
	(mx00.pub.collaborativefusion.com [206.210.89.199])
	by postgresql.org (Postfix) with ESMTP id EFE039F9E44
	for <pgsql-performance@postgresql.org>;
	Mon, 27 Aug 2007 11:45:21 -0300 (ADT)
Received: from vanquish.pitbpa0.priv.collaborativefusion.com
	(vanquish.pitbpa0.priv.collaborativefusion.com [192.168.2.61])
	(SSL: TLSv1/SSLv3,256bits,AES256-SHA)
	by wingspan with esmtp; Mon, 27 Aug 2007 10:45:20 -0400
	id 0005642A.46D2E380.000155CF
Date: Mon, 27 Aug 2007 10:45:20 -0400
From: Bill Moran <wmoran@collaborativefusion.com>
To: Willo van der Merwe <willo@mirasol.co.za>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issue
Message-Id: <20070827104520.d139433c.wmoran@collaborativefusion.com>
In-Reply-To: <46D2D496.60005@mirasol.co.za>
References: <46D2D496.60005@mirasol.co.za>
Organization: Collaborative Fusion
X-Mailer: Sylpheed 2.4.4 (GTK+ 2.10.14; i386-portbld-freebsd6.2)
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/323
X-Sequence-Number: 26453

In response to Willo van der Merwe <willo@mirasol.co.za>:

> Hi Guys,
> 
> I have something odd. I have Gallery2 running on PostgreSQL 8.1, and
> recently I upgraded to 8.1.9-1.el4s1.1 (64bit). The issue here really is
> how do I get PostgreSQL to work with their horrible code. The queries
> they generate look something like :
> SELECT blah, blah FROM table1, table2 WHERE <some relational stuff> AND
> id IN (<here a list of 42000+ IDs are listed>)
> 
> On the previous version (which I can't recall what it was, but it was a
> version 8.1) the queries executed fine, but suddenly now, these queries
> are taking up-to 4 minutes to complete. I am convinced it's the
> parsing/handling of the IN clause. It could, of course, be that the list
> has grown so large that it can't fit into a buffer anymore. For obvious
> reasons I can't run an EXPLAIN ANALYZE from a prompt.

Those reasons are not obvious to me.  The explain analyze output is
going to be key to working this out -- unless it's something like
your postgresql.conf isn't properly tuned.

> I vacuum and
> reindex  the database daily.
> 
> I'd prefer not to have to rewrite the code, so any suggestions would be
> very welcome.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023