SEO

#1 (**permalink**) 04-19-2008, 10:26 AM

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#2 (**permalink**) 04-19-2008, 10:26 AM

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#3 (**permalink**) 04-19-2008, 10:26 AM

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#4 (**permalink**) 04-19-2008, 10:26 AM

Hi Guys,

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

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

Kind regards

Willo van der Merwe

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Performance issue