Re: Fwd: 8.2 Query 10 times slower than 8.1 (view-heavy)

Dearest dragon hunters and mortal wanna-bes,

I recently upgraded a system from Apache2/mod_perl2 to
Lighttpd/fastcgi. The upgrade went about as rough as can be. While in
the midst of a bad day, I decided to make it worse, and upgrade Pg 8.1
to 8.2. Most people I talk to seem to think 8.1 was a lemon release;
not I. It worked excellent for me for the longest time, and I had no
good reason to upgrade it, other than to just have done so. In the
process, A query that took a matter of 2minutes, started taking hours.
I broke that query up into something more atomic and used it as a
sample.

The following material is provided for your assisting-me-pleasure: the
original SQL; the \ds for all pertinent views and tables; the output
of Explain Analyze; and the original query.

The original query both trials was: SELECT * FROM test_view where U_ID = 8;

test_view.sql = http://rafb.net/p/HhT9g489.html

8.1_explain_analyze = http://rafb.net/p/uIyY1s44.html
8.2_explain_analzye = http://rafb.net/p/mxHWi340.html

\d table/views = http://rafb.net/p/EPnyB229.html

Yes, I ran vacuum full after loading both dbs.

Thanks again, ask and I will provide anything else. I'm on freenode,
in #postgresql, and can be found at all times with the nick
EvanCarroll.

--
Evan Carroll
System Lord of the Internets
me(at)evancarroll(dot)com
832-445-8877

On 8/28/07, Evan Carroll <lists(at)evancarroll(dot)com> wrote:

> the midst of a bad day, I decided to make it worse, and upgrade Pg 8.1
> to 8.2. Most people I talk to seem to think 8.1 was a lemon release;
> not I.

8.0 was the release that had more issues for me, as it was the first
version with all the backend work done to make it capable of running
windows. for that reason I stayed on 7.4 until 8.1.4 or so was out.

8.2 was a nice incremental upgrade, and I migrated to it around 8.2.3
and have been happy every since.

> Yes, I ran vacuum full after loading both dbs.

Did you run analyze? It's not built into vacuum.

"Evan Carroll" <lists(at)evancarroll(dot)com> writes:

"Evan Carroll" <lists(at)evancarroll(dot)com> writes:

> Dearest dragon hunters and mortal wanna-bes,
>
> I recently upgraded a system from Apache2/mod_perl2 to
> Lighttpd/fastcgi. The upgrade went about as rough as can be. While in
> the midst of a bad day, I decided to make it worse, and upgrade Pg 8.1
> to 8.2. Most people I talk to seem to think 8.1 was a lemon release;
> not I. It worked excellent for me for the longest time, and I had no
> good reason to upgrade it, other than to just have done so.

I assume you mean 8.1.9 and 8.2.4?

> The following material is provided for your assisting-me-pleasure: the
> original SQL; the \ds for all pertinent views and tables; the output
> of Explain Analyze; and the original query.

While I do in fact enjoy analyzing query plans I have to say that 75-line
plans push the bounds of my assisting-you-pleasure. Have you experimented with
simplifying this query?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Gregory Stark wrote:
> "Evan Carroll" <lists(at)evancarroll(dot)com> writes:
>
> "Evan Carroll" <lists(at)evancarroll(dot)com> writes:
>
>> Dearest dragon hunters and mortal wanna-bes,
>>
>> I recently upgraded a system from Apache2/mod_perl2 to
>> Lighttpd/fastcgi. The upgrade went about as rough as can be. While in
>> the midst of a bad day, I decided to make it worse, and upgrade Pg 8.1
>> to 8.2. Most people I talk to seem to think 8.1 was a lemon release;
>> not I. It worked excellent for me for the longest time, and I had no
>> good reason to upgrade it, other than to just have done so.
>
> I assume you mean 8.1.9 and 8.2.4?
>
>> The following material is provided for your assisting-me-pleasure: the
>> original SQL; the \ds for all pertinent views and tables; the output
>> of Explain Analyze; and the original query.
>
> While I do in fact enjoy analyzing query plans I have to say that 75-line
> plans push the bounds of my assisting-you-pleasure. Have you experimented with
> simplifying this query?

Although simplifying the query is probably in order, doesn't it stand to
reason that there may be a problem here. 10x difference (in the worse)
from a lower version to a higher, is likely wrong :)

Joshua D. Drake

>

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG1EqYATb/zqfZUUQRAighAJ9g+Py+CRwsW7f5QWuA4uZ5G26a9gCcCXG2
0Le2KBGpdhDZyu4ZT30y8RA=
=MfQw
-----END PGP SIGNATURE-----

>>> On Tue, Aug 28, 2007 at 10:22 AM, in message
<428b865e0708280822u522391ecr5366bb5a6647f1aa(at)mail(dot)gmail(dot)com>, "Evan Carroll"
<lists(at)evancarroll(dot)com> wrote:
> Yes, I ran vacuum full after loading both dbs.

Have you run VACUUM ANALYZE or ANALYZE?

-Kevin

On 8/28/07, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> >>> On Tue, Aug 28, 2007 at 10:22 AM, in message
> <428b865e0708280822u522391ecr5366bb5a6647f1aa(at)mail(dot)gmail(dot)com>, "Evan Carroll"
> <lists(at)evancarroll(dot)com> wrote:
> > Yes, I ran vacuum full after loading both dbs.
>
> Have you run VACUUM ANALYZE or ANALYZE?

VACUUM FULL ANALYZE on both tables, out of habit.
>
> -Kevin
>
>
>
>

--
Evan Carroll
System Lord of the Internets
me(at)evancarroll(dot)com
832-445-8877

---------- Forwarded message ----------
From: Evan Carroll <me(at)evancarroll(dot)com>
Date: Aug 28, 2007 11:23 AM
Subject: Re: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy)
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

On 8/28/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> I looked through your query plan, and this is what stood out in the 8.2 plan:
>
> -> Nested Loop Left Join (cost=8830.30..10871.27 rows=1
> width=102) (actual time=2148.444..236018.971 rows=62 loops=1)
> Join Filter: ((public.contact.pkid =
> public.contact.pkid) AND (public.event.ts_in > public.event.ts_in))
> Filter: (public.event.pkid IS NULL)
>
> Notice the misestimation is by a factor of 62, and the actual time
> goes from 2149 to 236018 ms.
>
> Again, have you analyzed your tables / databases?
>
contacts=# \o scott_marlowe_test
contacts=# VACUUM FULL ANALYZE;
contacts=# SELECT * FROM test_view WHERE U_ID = 8;
Cancel request sent
ERROR: canceling statement due to user request
contacts=# EXPLAIN ANALYZE SELECT * FROM test_view WHERE U_ID = 8;

output found at http://rafb.net/p/EQouMI82.html

--
Evan Carroll
System Lord of the Internets
me(at)evancarroll(dot)com
832-445-8877

--
Evan Carroll
System Lord of the Internets
me(at)evancarroll(dot)com
832-445-8877

It looks like your view is using a left join to look for rows in one
table without matching rows in the other, i.e. a SQL construct similar
in form to the query below:

SELECT ...
FROM A LEFT JOIN B ON (...)
WHERE B.primary_key IS NULL

Unfortunately there has been a planner regression in 8.2 in some cases
with these forms of queries. This was discussed a few weeks (months?)
ago on this forum. I haven't looked closely enough to confirm that this
is the problem in your case, but it seems likely. Is it possible to
refactor the query to avoid using this construct to see if that helps?

We've been holding back from upgrading to 8.2 because this one is a
show-stopper for us.

-- Mark Lewis

On Tue, 2007-08-28 at 11:24 -0500, Evan Carroll wrote:
> ---------- Forwarded message ----------
> From: Evan Carroll <me(at)evancarroll(dot)com>
> Date: Aug 28, 2007 11:23 AM
> Subject: Re: [PERFORM] 8.2 Query 10 times slower than 8.1 (view-heavy)
> To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
>
>
> On 8/28/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> > I looked through your query plan, and this is what stood out in the 8.2 plan:
> >
> > -> Nested Loop Left Join (cost=8830.30..10871.27 rows=1
> > width=102) (actual time=2148.444..236018.971 rows=62 loops=1)
> > Join Filter: ((public.contact.pkid =
> > public.contact.pkid) AND (public.event.ts_in > public.event.ts_in))
> > Filter: (public.event.pkid IS NULL)
> >
> > Notice the misestimation is by a factor of 62, and the actual time
> > goes from 2149 to 236018 ms.
> >
> > Again, have you analyzed your tables / databases?
> >
> contacts=# \o scott_marlowe_test
> contacts=# VACUUM FULL ANALYZE;
> contacts=# SELECT * FROM test_view WHERE U_ID = 8;
> Cancel request sent
> ERROR: canceling statement due to user request
> contacts=# EXPLAIN ANALYZE SELECT * FROM test_view WHERE U_ID = 8;
>
> output found at http://rafb.net/p/EQouMI82.html
>
> --
> Evan Carroll
> System Lord of the Internets
> me(at)evancarroll(dot)com
> 832-445-8877
>
>

Mark Lewis <mark(dot)lewis(at)mir3(dot)com> writes:
> Unfortunately there has been a planner regression in 8.2 in some cases
> with these forms of queries. This was discussed a few weeks (months?)
> ago on this forum. I haven't looked closely enough to confirm that this
> is the problem in your case, but it seems likely.

Yeah, the EXPLAIN ANALYZE output clearly shows a drastic underestimate
of the number of rows out of a join like this, and a consequent choice
of a nestloop above it that performs terribly.

> We've been holding back from upgrading to 8.2 because this one is a
> show-stopper for us.

Well, you could always make your own version with this patch reverted:
http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php

I might end up doing that in the 8.2 branch if a better solution
seems too large to back-patch.

regards, tom lane

I wrote:
> Mark Lewis <mark(dot)lewis(at)mir3(dot)com> writes:
>> We've been holding back from upgrading to 8.2 because this one is a
>> show-stopper for us.

> Well, you could always make your own version with this patch reverted:
> http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php
> I might end up doing that in the 8.2 branch if a better solution
> seems too large to back-patch.

I thought of a suitably small hack that should cover at least the main
problem without going so far as to revert that patch entirely. What we
can do is have the IS NULL estimator recognize when the clause is being
applied at an outer join, and not believe the table statistics in that
case. I've applied the attached patch for this --- are you interested
in trying it out on your queries before 8.2.5 comes out?

regards, tom lane

On Fri, 2007-08-31 at 19:39 -0400, Tom Lane wrote:
> I wrote:
> > Mark Lewis <mark(dot)lewis(at)mir3(dot)com> writes:
> >> We've been holding back from upgrading to 8.2 because this one is a
> >> show-stopper for us.
>
> > Well, you could always make your own version with this patch reverted:
> > http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php
> > I might end up doing that in the 8.2 branch if a better solution
> > seems too large to back-patch.
>
> I thought of a suitably small hack that should cover at least the main
> problem without going so far as to revert that patch entirely. What we
> can do is have the IS NULL estimator recognize when the clause is being
> applied at an outer join, and not believe the table statistics in that
> case. I've applied the attached patch for this --- are you interested
> in trying it out on your queries before 8.2.5 comes out?

Wish I could, but I'm afraid that I'm not going to be in a position to
try out the patch on the application that exhibits the problem for at
least the next few weeks.

-- Mark