Обсуждение: Performance issue

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. I vacuum and
reindex  the database daily.

I'd prefer not to have to rewrite the code, so any suggestions would be
very welcome.

Kind regards

Willo van der Merwe

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

Willo van der Merwe <willo@mirasol.co.za> writes:
> 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.

You're wrong about that, because we have not done anything to change IN
planning in 8.1.x.  You might need to re-ANALYZE or something; it sounds
to me more like the planner has changed strategies in the wrong direction.

FWIW, 8.2 should be vastly more efficient than 8.1 for this sort of
query --- any chance of an upgrade?

            regards, tom lane

Hi Guys,

Following Tom Lane's advice I upgraded to 8.2, and that solved all my
problems. :D

Thank  you so much for your input, I really appreciate it.

Kind regards

Willo van der Merwe