X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 6B599D1C4DC for ; Mon, 21 Jul 2003 05:59:09 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 16833-03 for ; Mon, 21 Jul 2003 05:59:08 +0000 (GMT) Received: from mail.deltav.hu (oldmail.deltav.hu [213.163.0.192]) by svr1.postgresql.org (Postfix) with ESMTP id 93BCDD1C4BF for ; Mon, 21 Jul 2003 02:59:01 -0300 (ADT) Received: from fejleszt4 ([213.163.10.103]) by mail.deltav.hu (Netscape Messaging Server 3.6) with SMTP id AAA152F for ; Mon, 21 Jul 2003 07:59:00 +0200 Message-ID: <00f801c34f4d$2bf8d630$0403a8c0@fejleszt4> From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" To: Subject: Re: ugly query slower in 7.3, even slower after vacuum full analyze Date: Mon, 21 Jul 2003 07:58:56 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/319 X-Sequence-Number: 2612 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 -------------------------------