pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: severe performance issue with planner (fwd)
4+ messages / 3 participants
[nested] [flat]
* Re: severe performance issue with planner (fwd)
@ 2004-03-17 07:33 Kris Jurka <books@ejurka.com>
2004-03-18 05:03 ` Re: severe performance issue with planner (fwd) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Kris Jurka @ 2004-03-17 07:33 UTC (permalink / raw)
To: pgsql-performance
I sent this message to the list and although it shows up in the archives,
I did not receive a copy of it through the list, so I'm resending as I
suspect others did not see it either.
---------- Forwarded message ----------
Date: Sat, 13 Mar 2004 22:48:01 -0500 (EST)
From: Kris Jurka <books@ejurka.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Eric Brown <bigwhitecow@hotmail.com>, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] severe performance issue with planner
On Thu, 11 Mar 2004, Tom Lane wrote:
> "Eric Brown" <bigwhitecow@hotmail.com> writes:
> > [ planning a 9-table query takes too long ]
>
> See http://www.postgresql.org/docs/7.4/static/explicit-joins.html
> for some useful tips.
>
Is this the best answer we've got? For me with an empty table this query
takes 4 seconds to plan, is that the expected planning time? I know I've
got nine table queries that don't take that long.
Setting geqo_threshold less than 9, it takes 1 second to plan. Does this
indicate that geqo_threshold is set too high, or is it a tradeoff between
planning time and plan quality? If the planning time is so high because
the are a large number of possible join orders, should geqo_threhold be
based on the number of possible plans somehow instead of the number of
tables involved?
Kris Jurka
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: severe performance issue with planner (fwd)
2004-03-17 07:33 Re: severe performance issue with planner (fwd) Kris Jurka <books@ejurka.com>
@ 2004-03-18 05:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-03-18 05:03 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: pgsql-performance
Kris Jurka <books@ejurka.com> writes:
> On Thu, 11 Mar 2004, Tom Lane wrote:
>> "Eric Brown" <bigwhitecow@hotmail.com> writes:
>>> [ planning a 9-table query takes too long ]
>>
>> See http://www.postgresql.org/docs/7.4/static/explicit-joins.html
>> for some useful tips.
> Is this the best answer we've got? For me with an empty table this query
> takes 4 seconds to plan, is that the expected planning time? I know I've
> got nine table queries that don't take that long.
The problem with this example is that it's a nine-way self-join.
Ordinarily the planner can eliminate many possible join paths at low
levels, because they are more expensive than other available options.
But in this situation all the available options have *exactly the same
cost estimate* because they are all founded on exactly the same statistics.
The planner fails to prune any of them and ends up making a random
choice after examining way too many alternatives.
Maybe we should think about instituting a hard upper limit on the number
of alternatives considered. But I'm not sure what the consequences of
that would be. In the meantime, the answer for the OP is to arbitrarily
limit the number of join orders considered, as described in the
above-mentioned web page. With the given query constraints there's
really only one join order worth thinking about ...
> Setting geqo_threshold less than 9, it takes 1 second to plan. Does this
> indicate that geqo_threshold is set too high, or is it a tradeoff between
> planning time and plan quality?
Selecting the GEQO planner doesn't really matter here, because it has
no better clue about how to choose among a lot of alternatives with
identical cost estimates.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: severe performance issue with planner (fwd)
@ 2004-03-19 01:23 Eric Brown <bigwhitecow@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Eric Brown @ 2004-03-19 01:23 UTC (permalink / raw)
To: pgsql-performance
I think i just sent an email out to the qrong person.. so if this ends up in
the list 2x i'm very sorry:
i changed the query to:SELECT
w8.wid,
w8.variant,
w8.num_variants,
sum_text(w8.unicode) as unicodes,
sum_text(w8.pinyin) as pinyins
FROM
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_')
JOIN words as w2 ON(w2.wid = w1.wid AND w2.variant = w1.variant AND
w2.sequence = w1.sequence + 1 AND w2.pinyin LIKE 'ji_')
JOIN words as w3 ON(w3.wid = w2.wid AND w3.variant = w2.variant AND
w3.sequence = w2.sequence + 1 AND w3.pinyin LIKE 'guan_')
JOIN words as w4 ON(w4.wid = w3.wid AND w4.variant = w3.variant AND
w4.sequence = w3.sequence + 1 AND w4.pinyin LIKE 'kai_')
JOIN words as w5 ON(w5.wid = w4.wid AND w5.variant = w4.variant AND
w5.sequence = w4.sequence + 1 AND w5.pinyin LIKE 'fang_')
JOIN words as w6 ON(w6.wid = w5.wid AND w6.variant = w5.variant AND
w6.sequence = w5.sequence + 1 AND w6.pinyin LIKE 'xi_')
JOIN words as w7 ON(w7.wid = w6.wid AND w7.variant = w6.variant AND
w7.sequence = w6.sequence + 1 AND w7.pinyin LIKE 'tong_')
JOIN words as w8 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;
and this cuts the time from 2900ms to about 1200ms. Is there any way to get
better time since the prepared statements for this explicit join query is
about 320ms...
thx so far guys
>
>Kris Jurka <books@ejurka.com> writes:
> > On Thu, 11 Mar 2004, Tom Lane wrote:
> >> "Eric Brown" <bigwhitecow@hotmail.com> writes:
> >>> [ planning a 9-table query takes too long ]
> >>
> >> See http://www.postgresql.org/docs/7.4/static/explicit-joins.html
> >> for some useful tips.
>
> > Is this the best answer we've got? For me with an empty table this
>query
> > takes 4 seconds to plan, is that the expected planning time? I know
>I've
> > got nine table queries that don't take that long.
>
>The problem with this example is that it's a nine-way self-join.
>Ordinarily the planner can eliminate many possible join paths at low
>levels, because they are more expensive than other available options.
>But in this situation all the available options have *exactly the same
>cost estimate* because they are all founded on exactly the same statistics.
>The planner fails to prune any of them and ends up making a random
>choice after examining way too many alternatives.
>
>Maybe we should think about instituting a hard upper limit on the number
>of alternatives considered. But I'm not sure what the consequences of
>that would be. In the meantime, the answer for the OP is to arbitrarily
>limit the number of join orders considered, as described in the
>above-mentioned web page. With the given query constraints there's
>really only one join order worth thinking about ...
>
> > Setting geqo_threshold less than 9, it takes 1 second to plan. Does
>this
> > indicate that geqo_threshold is set too high, or is it a tradeoff
>between
> > planning time and plan quality?
>
>Selecting the GEQO planner doesn't really matter here, because it has
>no better clue about how to choose among a lot of alternatives with
>identical cost estimates.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar get it now!
http://clk.atdmt.com/AVE/go/onm00200415ave/direct/01/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: severe performance issue with planner (fwd)
@ 2004-03-19 01:59 Eric Brown <bigwhitecow@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Eric Brown @ 2004-03-19 01:59 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-03-19 01:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-17 07:33 Re: severe performance issue with planner (fwd) Kris Jurka <books@ejurka.com>
2004-03-18 05:03 ` Re: severe performance issue with planner (fwd) Tom Lane <tgl@sss.pgh.pa.us>
2004-03-19 01:23 Re: severe performance issue with planner (fwd) Eric Brown <bigwhitecow@hotmail.com>
2004-03-19 01:59 Re: severe performance issue with planner (fwd) Eric Brown <bigwhitecow@hotmail.com>
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