Re: ugly query slower in 7.3, even slower after vacuum full analyze

Dear Gurus,

This is a rather nasty query, built up from several parameters, and it
proved to be 7--15 times slower in 7.3 than in 7.2. This particular query
takes more than 3.5 minutes (4 after vacuum full analyze! (henceforth VFA))
that is unacceptable in an interactive client application.

If you have courage and will to please have a look at the query and/or the
explains, you might point out something I can't see at this level of
complexity.

As for trivial questions:

* The databases were identical a couple of weeks ago, deviated slightly
since then, but I don't think it may be a cause.
* The 5% difference in the result set doesn't seem to explain this huge
diff in performance either.
* The query has been run on the same server (Linux RedHat 6.1 --
historical, isn't it?) with the same load (this one postmaster took >90%
CPU all the time, in all three cases)
* Since this query involves quite a large part of the database, I'm not
willing to post a dump on the list. If a schema-only dump helps, I may
be able to send it in private email; I approximate it to be ~500k,
zipped.
* Also checked a "lighter" version of this query (at least, fewer rows). It
took 223msec on 7.2 and 3658 on 7.3 (VFA). (15x slower) However, it got
down to 400-500msec (still double of 7.2) when re-queried

Files are zipped, since 7.3 exp-ana's are over 40k each.

slow.sql: the query.
72.ana: explain analyze in 7.2
73.ana: explain analyze in 7.3, before VFA
73.ana2: explain analyze in 7.3, after VFA

I just hope someone helps me; any little help may prove really useful!
TIA,
G.
------------------------------- cut here -------------------------------

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano(at)mailbox(dot)hu> writes:
> This is a rather nasty query, built up from several parameters, and it
> proved to be 7--15 times slower in 7.3 than in 7.2.

I think you are running into the same subselect-in-targetlist
shortcoming as Eugene Fokin did:
http://archives.postgresql.org/pgsql-performance/2003-05/msg00204.php

regards, tom lane

Szucs,

> This is a rather nasty query, built up from several parameters, and it
> proved to be 7--15 times slower in 7.3 than in 7.2. This particular query
> takes more than 3.5 minutes (4 after vacuum full analyze! (henceforth VFA))
> that is unacceptable in an interactive client application.

Please read the list archives for the last 3-4 days. Another user reported a
"slow query" problem with 7.3.2; please see if it sounds like yours.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Dear Tom, (or anyone who followed the belowmentioned thread)

I read that thread (more-or-less), but couldn't have noticed the same
symptoms in my analyze output. So, to summarize my reading on this (please
confirm or fix):

* The symptom is the differing width in 7.2 and 7.3

* This causes more hdd work, that takes lots of time (indeed, the hdd was
going crazy)

* The query is probably good as it is; it's 7.3 that's slow (but more
reliable than 7.2) and 7.4 will most likely fix the problem.

If all these are correct, that's enough info to me. Hopefully it'll move
from a Cel333 (the developers' server) to an IBM 2x2.4 Xeon with 5-HDD SCSI
Raid (the business server).

G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, May 22, 2003 6:02 PM
Subject: Re: [PERFORM] ugly query slower in 7.3, even slower after vacuum
full analyze

> "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano(at)mailbox(dot)hu> writes:
> > This is a rather nasty query, built up from several parameters, and it
> > proved to be 7--15 times slower in 7.3 than in 7.2.
>
> I think you are running into the same subselect-in-targetlist
> shortcoming as Eugene Fokin did:
> http://archives.postgresql.org/pgsql-performance/2003-05/msg00204.php
>
> regards, tom lane
>