From: SZŰCS Gábor <surrano@mailbox.hu>
To: pgsql-performance@postgresql.org
Cc: TIR@yahoogroups.com
Subject: ugly query slower in 7.3, even slower after vacuum full analyze
Date: Thu, 22 May 2003 16:25:54 +0200
Message-ID: <008101c3206e$0dc8a310$0403a8c0@fejleszt4> (raw)
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 -------------------------------
Attachments:
slow_and_ugly.zip (application/octet-stream, 11.4K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: surrano@mailbox.hu, TIR@yahoogroups.com
Subject: Re: ugly query slower in 7.3, even slower after vacuum full analyze
In-Reply-To: <008101c3206e$0dc8a310$0403a8c0@fejleszt4>
References: <008101c3206e$0dc8a310$0403a8c0@fejleszt4>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox