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 -------------------------------
