Possible planner bug/regression introduced in 8.2.5

From: Jakub Ouhrabka <kuba(at)comgate(dot)cz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Possible planner bug/regression introduced in 8.2.5
Date: 2007-10-24 08:57:18
Message-ID: 471F08EE.7040009@comgate.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

we switched from 8.2.4 to 8.2.5 and were forced to switch back. Our main
goal was to improve planner perfomance with outer joins per this change
from release notes:

Fix some planner problems with outer joins, notably poor size estimation
for t1 LEFT JOIN t2 WHERE t2.col IS NULL (Tom)

But it seems that this change introduced (or exhibited) new bug/regression.

We can't get even query plan (explain) for some queries. The server
process starts to chew up memory then server starts to swap and then we
have to kill the process. On 8.2.4 the query works fine.

I don't have self-contained test case but I'll try to provide as much
details as possible.

The simplified query but still causing the problem looks like this:

SELECT
1 -- select list doesn't matter
FROM
a JOIN b ON (pk = fk)
b JOIN c ON (...)

[ 13 joins like this in total, not exactly star query ]

LEFT OUTER JOIN x ON (pk = fk)
LEFT OUTER JOIN y ON (pk = fk)

-- WHERE clause doesn't matter
;

There are cca 15 tables involved, if I remove one of the outer joins,
query is planned ok. Some of the tables are big (millions), some are
small. There are many fks between the tables and many indexes (most if
not all join conditions are indexed).

The behaviour is exhibited when "normal" planner is used, not geqo.

Statistics target is set to 500.

I tried to simplify the query even more and now I have query which on
8.2.4 is planned instantly and on 8.2.5 takes cca 8 seconds. The query
and query plan is attached. All entities are tables, views were eliminated.

What more info should I provide? What can I do to debug this problem?
Can someone see the cause offhand from the planner changes in 8.2.5?

On (possibly) unrelated note: what is recommanded maximum for
geqo_threshold? We don't care if planning takes few seconds and produces
good plan - our experience with non-deterministic geqo for queries with
many big tables is not the best one (the plans vary too much between
subsequent runs).

Thanks,

Kuba

Attachment Content-Type Size
problem.txt text/plain 6.0 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bill Moran 2007-10-24 12:47:25 Re: BUG #3692: Conflicting create table statements throw unexpected error
Previous Message Alvaro Herrera 2007-10-23 12:56:58 Re: BUG #3692: Conflicting create table statements throw unexpected error