X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 51A26D1C4E0 for ; Fri, 12 Mar 2004 18:00:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 03277-03 for ; Fri, 12 Mar 2004 14:00:40 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 85585D1B979 for ; Fri, 12 Mar 2004 14:00:34 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id B4DA517C312; Fri, 12 Mar 2004 13:00:33 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1B1qxV-0003Xd-00; Fri, 12 Mar 2004 13:00:33 -0500 To: "Eric Brown" Cc: pgsql-performance@postgresql.org Subject: Re: severe performance issue with planner References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 12 Mar 2004 13:00:33 -0500 Message-ID: <873c8e54ji.fsf@stark.xeocode.com> Lines: 58 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/160 X-Sequence-Number: 6016 The other posts about using explicit joins and using stored procedures are both good points. But I have a few other comments to make: "Eric Brown" writes: > WHERE > w0.wid > 0 AND > w0.pinyin = 'zheng4' AND > w0.def_exists = 't' AND > w0.sequence = 0 AND > w1.wid = w0.wid AND > w1.pinyin LIKE 'fu_' AND > w1.variant = w0.variant AND > w1.sequence = (w0.sequence + 1) AND I'm not sure it'll help the planner, but w0.sequence+1 is always just going to be 1, and so on with the others. I think the planner might be able to figure that out but the plan doesn't seem to show it doing so. I'm not sure it would help the plan though. Similarly you have w1.wid=w0.wid and w2.wid=w1.wid and w3.wid=w2.wid etc. And also with the "variant" column. You might be able to get this planned better by writing it as a join from w0 to all the others rather than a chain of w0->w1->w2->... Again I'm not sure; you would have to experiment. But I wonder if there isn't a way to do this in a single pass using an aggregate. I'm not sure I understand the schema exactly, but perhaps something like this? select w8.wid, w8.variant, w8.num_variants, sum_text(w8.unicode) as unicodes, sum_text(w8.pinyin) as pinyins from ( select wid,variant, from words where (sequence = 0 and pinyin = 'zheng4') OR (sequence = 1 and pinyin like 'ji_') OR (sequence = 2 and pinyin like 'guan_') OR (sequence = 3 and pinyin like 'kai_') OR (sequence = 4 and pinyin like 'fang_') OR (sequence = 5 and pinyin like 'xi_') OR (sequence = 6 and pinyin like 'tong_') OR (sequence = 7 and pinyin like 'fu_') group by wid,variant having count(*) = 8 ) as w join words as w8 using (wid,variant) This might be helped by having an index on but it might not even need it. -- greg