Thanks Tom and Alvaro.
To follow up on this, I re-wrote and tweaked a number of queries (including
the one provided) to change "LEFT OUTER JOIN ... WHERE col IS NULL" clauses
to "WHERE col NOT IN (...)" clauses.
This has brought performance to an acceptable level on 8.2.
Thanks for your time,
S
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane escribió:
>> I was hoping that the auto plan invalidation code in CVS HEAD would get
>> it out of this problem, but it seems not to for the problem-as-given.
>> The trouble is that it won't change plans until autovacuum analyzes the
>> tables, a
Tom Lane escribió:
> I was hoping that the auto plan invalidation code in CVS HEAD would get
> it out of this problem, but it seems not to for the problem-as-given.
> The trouble is that it won't change plans until autovacuum analyzes the
> tables, and that won't happen until the transaction commi
"Steven Flatt" <[EMAIL PROTECTED]> writes:
> One instance of our problem goes like this, and I have included a
> self-contained example with which you can reproduce the problem.
This is fairly interesting, because if you run the query by hand after
the function finishes, it's pretty fast. What I
On 6/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:
If you're feeling desperate you could revert this patch in your local
copy:
http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php
regards, tom lane
Reverting that patch has not appeared to solve our problem.
"Steven Flatt" <[EMAIL PROTECTED]> writes:
> Is there any estimation as to if/when the fix will
> become available? I'm hoping this isn't going to be a showstopper in us
> moving to 8.2.
If you're feeling desperate you could revert this patch in your local
copy:
http://archives.postgresql.org/pgs
On Tue, Jun 05, 2007 at 05:30:14PM -0400, Steven Flatt wrote:
> (A) LEFT JOIN (B) ON col WHERE B.col IS NULL
>
> These queries are much slower on 8.2 than on 8.1 for what looks like the
> reason outlined above. I have rewritten a few key queries to be of the
> equivalent form:
>
> (A) WHERE col
On 5/18/07, Tom Lane <[EMAIL PROTECTED]> wrote:
Yeah. 8.2 is estimating that the "nodeid IS NULL" condition will
discard all or nearly all the rows, presumably because there aren't any
null nodeid's in the underlying table --- it fails to consider that the
LEFT JOIN may inject some nulls. 8.1
On May 18, 2007, at 11:40 AM, Liviu Ionescu wrote:
8.1 might have similar problems, but the point here is different:
if what
was manually tuned to work in 8.1 confuses the 8.2 planner and
performance
drops so much (from 2303 to 231929 ms in my case) upgrading a
production
machine to 8.2 i
On 18.05.2007, at 10:21, Kenneth Marshall wrote:
It is arguable, that updating the DB software version in an enterprise
environment requires exactly that: check all production queries on the
new software to identify any issues. In part, this is brought on by
the
very tuning that you performed
On Fri, May 18, 2007 at 06:40:31PM +0300, Liviu Ionescu wrote:
> > under some alignments of the planets 8.1 has similar problems.
>
> 8.1 might have similar problems, but the point here is different: if what
> was manually tuned to work in 8.1 confuses the 8.2 planner and performance
> drops so mu
> It is arguable, that updating the DB software version in an
> enterprise environment requires exactly that: check all
> production queries on the new software to identify any
> issues. In part, this is brought on by the very tuning that
> you performed against the previous software. Restore t
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> On Fri, May 18, 2007 at 02:05:36PM +0300, Liviu Ionescu wrote:
>> "-> Hash Left Join
>> (cost=2.44..63.29 rows=1 width=49) (actual time=0.361..14.426 rows=2206
>> loops=1)"
>> "
> under some alignments of the planets 8.1 has similar problems.
8.1 might have similar problems, but the point here is different: if what
was manually tuned to work in 8.1 confuses the 8.2 planner and performance
drops so much (from 2303 to 231929 ms in my case) upgrading a production
machine to
> > This seems to be the source of the misestimation. You might
> > want to try using "n WHERE n.nodein NOT IN (SELECT nodeid
> > FROM templates)" instead of "n LEFT JOIN templates USING
> > (nodeid) WHERE templates.nodeid IS NULL" and see if it helps.
>
> it helped, the new version of the quer
> > it helped, the new version of the query takes 2303 ms on both 8.1.4
> > and 8.2.4.
>
> And the old one?
slightly shorter, 2204 ms.
as a subjective perception, the entire application is slightly slower on 8.2.4,
probably there are many queries that were manually tunned for 7.x/8.1.x and now
On Fri, May 18, 2007 at 02:51:42PM +0300, Liviu Ionescu wrote:
> it helped, the new version of the query takes 2303 ms on both 8.1.4 and 8.2.4.
And the old one?
> any idea why the 8.2.4 planner is not happy with the initial select? was it
> just a big chance that it worked in 8.1.4 or the 8.2.4 p
> This seems to be the source of the misestimation. You might
> want to try using "n WHERE n.nodein NOT IN (SELECT nodeid
> FROM templates)" instead of "n LEFT JOIN templates USING
> (nodeid) WHERE templates.nodeid IS NULL" and see if it helps.
it helped, the new version of the query takes 2303
On Fri, May 18, 2007 at 02:05:36PM +0300, Liviu Ionescu wrote:
> "-> Hash Left Join
> (cost=2.44..63.29 rows=1 width=49) (actual time=0.361..14.426 rows=2206
> loops=1)"
> " Hash Cond: (n.nodeid =
> te
> Is this with the join collapse limit set to 1, or with
> default? (Default is generally more interesting.)
below is the same query with the default setting.
regards,
Liviu
"Nested Loop Left Join (cost=23.35..1965.46 rows=1 width=125) (actual
time=50.408..231926.123 rows=2026 loops=1)"
"
On Fri, May 18, 2007 at 01:14:56PM +0300, Liviu Ionescu wrote:
> yes, but to be sure I did it again before issuing the request; no
> improvements...
Is this with the join collapse limit set to 1, or with default? (Default is
generally more interesting.)
/* Steinar */
--
Homepage: http://www.ses
> It will probably be useful with EXPLAIN ANALYZE of your
> queries, not just the EXPLAIN.
it took 245 seconds to complete, see below.
> It looks like the planner thinks this is going to be really
> cheap -- so it's misestimating something somewhere. Have you
> ANALYZEd recently?
yes, but to
On Fri, May 18, 2007 at 12:02:44PM +0300, Liviu Ionescu wrote:
> the 8.2.4 plan with join_collapse_limit = 1 (with default it was worse, full
> of nested loops)
It will probably be useful with EXPLAIN ANALYZE of your queries, not just the
EXPLAIN.
> "Nested Loop Left Join (cost=32.01..2012.31 r
23 matches
Mail list logo
