Re: [SQL] Egroupware infolog query slow (includes query plan)

Mark Stosberg <mark@summersault.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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql