pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eric Brown <bigwhitecow@hotmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: severe performance issue with planner (fwd)
Date: Thu, 18 Mar 2004 20:59:39 -0500
Message-ID: <BAY13-F1111d7lQlpo60003142a@hotmail.com> (raw)

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/




view thread (4+ messages)

Message-ID: <BAY13-F1111d7lQlpo60003142a@hotmail.com>
Permalink:  ../BAY13-F1111d7lQlpo60003142a@hotmail.com/
Also on:    postgresql.org/message-id/BAY13-F1111d7lQlpo60003142a@hotmail.com

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: bigwhitecow@hotmail.com
  Subject: Re: severe performance issue with planner (fwd)
  In-Reply-To: <BAY13-F1111d7lQlpo60003142a@hotmail.com>

* 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