Skip site navigation (1) Skip section navigation (2)

Re: Performance issue

From: Bill Moran <wmoran(at)collaborativefusion(dot)com>
To: Willo van der Merwe <willo(at)mirasol(dot)co(dot)za>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance issue
Date: 2007-08-27 14:45:20
Message-ID: 20070827104520.d139433c.wmoran@collaborativefusion.com (view raw or flat)
Thread:
Lists: pgsql-performance
In response to Willo van der Merwe <willo(at)mirasol(dot)co(dot)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(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2007-08-27 15:55:46
Subject: Re: Performance issue
Previous:From: Vivek KheraDate: 2007-08-27 14:43:19
Subject: Re: [PERFORM] Partioning tsearch2 a table into chunks and accessing via views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group