Thread: 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 -------------------------------
Attachment
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.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@sss.pgh.pa.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@mailbox.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 >
Dear Gurus, I have a query discussed here earlier that suffers heavily from "lack of view flattening" in v7.3. Following Tom's guidance, I made a conclusion to that thread (http://archives.postgresql.org/pgsql-performance/2003-05/msg00215.php) and asked it to be confirmed or fixed, but I didn't get any responses. Here are some times, for which I'd like to get some response. Old machine is New machine is * PIII 800, * Dual Xeon 2.4, * IDE 7200, * 5xSCSI 10000 HW RAID 5, * psql 7.2.1, * psql 7.3.3, * orig conf * orig and crude conf, as below. * old: 18 sec * new: 24 sec * new w/ vacuum full verbose analyze: 30-31 sec (!!!) 1. Are these times (18 vs 24) believable with such heavy HW change or is there something fishy about it? * I know multiprocessing doesn't come in view with a single query * but cpu and hw speed should * I know 7.3 is slower because of unflattened views 2. What may be the cause of VACUUM slowing the query? 3. Disabling any one of mergejoin, hashjoin, seqscan did no good. Disabling sort prevented query from finishing in several minutes. 4. I have tried to crudely carve optimizer settings as below, but it changed nothing according to this query. Any further ideas? Note that time tests were taken in close succession (test; killall -HUP postmaster; test; ...) If needed, I can attach query, exp-ana outputs before and after vacuum (carved and uncarved conf file), and the vacuum log itself. TIA, G. ------------------------------- cut here ------------------------------- shared_bufers = 4096 sort_mem = 4096 effective_cache_size = 20000 random_page_cost = 1.5 ------------------------------- cut here -------------------------------