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

Re: Egroupware infolog query slow (includes query plan)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Stosberg <mark(at)summersault(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Egroupware infolog query slow (includes query plan)
Date: 2008-07-06 21:34:27
Message-ID: 17233.1215380067@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Mark Stosberg <mark(at)summersault(dot)com> writes:
> I'm not skilled enough at reading the "Explain Analzyze" output to
> understand what the primary problem is. 

The problem is the repeated execution of the subquery in the SELECT
list; that's taking over 683 of the 686 seconds:

>                     SubPlan
>                       ->  Aggregate  (cost=2162.60..2162.61 rows=1
> width=0) (actual time=21.073..21.073 rows=1 loops=32424)
                                ^^^^^^              ^^^^^

The current formulation of the query guarantees that you can't do better
than a nestloop join with "sub" on the inside, and that nestloop isn't
even indexed.  See if you can convert it to a regular join instead of a
sub-select (probably with GROUP BY instead of DISTINCT).

Also, those LIKE conditions are just horrid: slow *and* unreadable.
Consider redesigning your data representation.  Perhaps converting
info_responsible to an int array would be reasonable.

			regards, tom lane

In response to

pgsql-sql by date

Next:From: dipeshDate: 2008-07-07 09:13:15
Subject: How to find space occupied by postgres on harddisk
Previous:From: Mark StosbergDate: 2008-07-06 20:26:51
Subject: Re: Egroupware infolog query slow (includes query plan)

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