From: Greg Stark <gsstark@mit.edu>
To: Eric Brown <bigwhitecow@hotmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: severe performance issue with planner
Date: 12 Mar 2004 13:00:33 -0500
Message-ID: <873c8e54ji.fsf@stark.xeocode.com> (raw)
In-Reply-To: <BAY13-F54DwKAYzTkuA00017c9b@hotmail.com>
References: <BAY13-F54DwKAYzTkuA00017c9b@hotmail.com>
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" <bigwhitecow@hotmail.com> 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 <sequence,pinyin> but it might not
even need it.
--
greg
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: gsstark@mit.edu, bigwhitecow@hotmail.com
Subject: Re: severe performance issue with planner
In-Reply-To: <873c8e54ji.fsf@stark.xeocode.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