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 3BCDCD1B904 for ; Fri, 19 Mar 2004 01:59:33 +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 06230-05 for ; Thu, 18 Mar 2004 21:59:39 -0400 (AST) Received: from hotmail.com (bay13-f111.bay13.hotmail.com [64.4.31.111]) by svr1.postgresql.org (Postfix) with ESMTP id 2C61AD1B566 for ; Thu, 18 Mar 2004 21:59:31 -0400 (AST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 18 Mar 2004 17:59:40 -0800 Received: from 68.38.165.44 by by13fd.bay13.hotmail.msn.com with HTTP; Fri, 19 Mar 2004 01:59:39 GMT X-Originating-IP: [68.38.165.44] X-Originating-Email: [bigwhitecow@hotmail.com] X-Sender: bigwhitecow@hotmail.com From: "Eric Brown" To: pgsql-performance@postgresql.org Subject: Re: severe performance issue with planner (fwd) Date: Thu, 18 Mar 2004 20:59:39 -0500 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 19 Mar 2004 01:59:40.0253 (UTC) FILETIME=[D6EE28D0:01C40D55] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/275 X-Sequence-Number: 6131 I also tried this (printf1 in irc suggested "folding" the joins) : SELECT w8.wid, w8.variant, w8.num_variants, sum_text(w8.unicode) as unicodes, sum_text(w8.pinyin) as pinyins FROM (words as w8 JOIN (words as w7 JOIN (words as w6 JOIN (words as w5 JOIN (words as w4 JOIN (words as w3 JOIN (words as w2 JOIN (words as w0 JOIN words as w1 ON(w1.wid = w0.wid AND w1.variant = w0.variant AND w1.sequence = w0.sequence + 1 AND w1.pinyin LIKE 'fu_')) ON(w2.wid = w1.wid AND w2.variant = w1.variant AND w2.sequence = w1.sequence + 1 AND w2.pinyin LIKE 'ji_')) ON(w3.wid = w2.wid AND w3.variant = w2.variant AND w3.sequence = w2.sequence + 1 AND w3.pinyin LIKE 'guan_')) ON(w4.wid = w3.wid AND w4.variant = w3.variant AND w4.sequence = w3.sequence + 1 AND w4.pinyin LIKE 'kai_')) ON(w5.wid = w4.wid AND w5.variant = w4.variant AND w5.sequence = w4.sequence + 1 AND w5.pinyin LIKE 'fang_')) ON(w6.wid = w5.wid AND w6.variant = w5.variant AND w6.sequence = w5.sequence + 1 AND w6.pinyin LIKE 'xi_')) ON(w7.wid = w6.wid AND w7.variant = w6.variant AND w7.sequence = w6.sequence + 1 AND w7.pinyin LIKE 'tong_')) ON(w8.wid = w7.wid AND w8.variant = w7.variant)) WHERE w0.wid > 0 AND w0.pinyin = 'zheng4' AND w0.def_exists = 't' AND w0.sequence = 0 GROUP BY w8.wid, w8.variant, w8.num_variants, w8.page_order, w0.sequence , w1.sequence , w2.sequence , w3.sequence , w4.sequence , w5.sequence , w6.sequence , w7.sequence ORDER BY w8.page_order; this gets teh time down to 800ms (not too shabby..).. and as a prepared statement, it only takes 15ms!!! i am hopeful there is a way to totally bypass most of this overhead.. but i need more help :\ _________________________________________________________________ MSN Toolbar provides one-click access to Hotmail from any Web page – FREE download! http://clk.atdmt.com/AVE/go/onm00200413ave/direct/01/