Possible planner bug/regression introduced in 8.2.5 - Mailing list 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


-- all entities are tables, views were eliminated

explain
SELECT
  1
        FROM obchodni_pripad_verze ov
            JOIN obchodni_pripad op ON op.obchodni_pripad_pk = ov.obchodni_pripad_pk
            JOIN stav_pripadu sp ON sp.stav_pripadu_pk = ov.stav_pripadu_pk
            JOIN stav_pripadu_ciselnik sc ON sc.stav_pripadu_ciselnik_id = sp.stav_pripadu_ciselnik_id
            JOIN fronta fr ON fr.fronta_id = sp.fronta_id
            JOIN kontakt ko ON ko.kontakt_pk = op.kontakt_pk
            JOIN kontakt_verze kov ON kov.kontakt_pk = ko.kontakt_pk
            JOIN kampan ka ON ka.kampan_pk = op.kampan_pk
            JOIN produkt pr ON pr.produkt_id = ka.produkt_id
            JOIN uzivatel uz_ko ON uz_ko.uzivatel_pk = kov.uzivatel_pk_ulozil
            JOIN abc_obchodni_pripad abc_pripad ON abc_pripad.obchodni_pripad_verze_pk = ov.obchodni_pripad_verze_pk
            LEFT OUTER JOIN adresa adresa_trvala ON adresa_trvala.adresa_pk = abc_pripad.adresa_pk_trvala
            LEFT OUTER JOIN abc_ciselnik abc_ciselnik_abc_pripad_produkt ON
abc_ciselnik_abc_pripad_produkt.abc_ciselnik_id= abc_pripad.produkt 
;

                                                                                             QUERY PLAN
                                      

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=14433.26..18497.99 rows=1811 width=0)
   Hash Cond: (abc_pripad.produkt = abc_ciselnik_abc_pripad_produkt.abc_ciselnik_id)
   ->  Hash Join  (cost=14432.17..18490.02 rows=1811 width=4)
         Hash Cond: (kov.uzivatel_pk_ulozil = uz_ko.uzivatel_pk)
         ->  Nested Loop  (cost=14424.58..18457.53 rows=1811 width=8)
               ->  Nested Loop  (cost=14424.58..18115.40 rows=332 width=12)
                     ->  Nested Loop Left Join  (cost=14424.58..17998.25 rows=332 width=8)
                           ->  Hash Join  (cost=14424.58..15756.76 rows=332 width=12)
                                 Hash Cond: (op.kampan_pk = ka.kampan_pk)
                                 ->  Nested Loop  (cost=14420.25..15742.63 rows=1726 width=16)
                                       ->  Hash Join  (cost=14420.25..15188.26 rows=1726 width=12)
                                             Hash Cond: (abc_pripad.obchodni_pripad_verze_pk =
ov.obchodni_pripad_verze_pk)
                                             ->  Seq Scan on abc_obchodni_pripad abc_pripad  (cost=0.00..610.91
rows=37291width=12) 
                                             ->  Hash  (cost=12707.95..12707.95 rows=136984 width=8)
                                                   ->  Nested Loop  (cost=287.49..12707.95 rows=136984 width=8)
                                                         ->  Nested Loop  (cost=1.34..11.62 rows=9 width=4)
                                                               ->  Hash Join  (cost=1.34..6.21 rows=15 width=8)
                                                                     Hash Cond: (sp.fronta_id = fr.fronta_id)
                                                                     ->  Seq Scan on stav_pripadu sp  (cost=0.00..3.98
rows=198width=12) 
                                                                     ->  Hash  (cost=1.15..1.15 rows=15 width=4)
                                                                           ->  Seq Scan on fronta fr  (cost=0.00..1.15
rows=15width=4) 
                                                               ->  Index Scan using
stav_pripadu_ciselnik_stav_pripadu_ciselnik_id_keyon stav_pripadu_ciselnik sc  (cost=0.00..0.35 rows=1 width=4) 
                                                                     Index Cond: (sc.stav_pripadu_ciselnik_id =
sp.stav_pripadu_ciselnik_id)
                                                         ->  Bitmap Heap Scan on obchodni_pripad_verze ov
(cost=286.15..1162.48rows=19858 width=12) 
                                                               Recheck Cond: (sp.stav_pripadu_pk = ov.stav_pripadu_pk)
                                                               ->  Bitmap Index Scan on
obchodni_pripad_verze_stav_pripadu_pk_idx (cost=0.00..281.19 rows=19858 width=0) 
                                                                     Index Cond: (sp.stav_pripadu_pk =
ov.stav_pripadu_pk)
                                       ->  Index Scan using obchodni_pripad_pkey on obchodni_pripad op
(cost=0.00..0.31rows=1 width=12) 
                                             Index Cond: (op.obchodni_pripad_pk = ov.obchodni_pripad_pk)
                                 ->  Hash  (cost=4.08..4.08 rows=20 width=4)
                                       ->  Hash Join  (cost=1.45..4.08 rows=20 width=4)
                                             Hash Cond: (ka.produkt_id = pr.produkt_id)
                                             ->  Seq Scan on kampan ka  (cost=0.00..2.04 rows=104 width=8)
                                             ->  Hash  (cost=1.20..1.20 rows=20 width=4)
                                                   ->  Seq Scan on produkt pr  (cost=0.00..1.20 rows=20 width=4)
                           ->  Index Scan using adresa_pkey on adresa adresa_trvala  (cost=0.00..6.74 rows=1 width=4)
                                 Index Cond: (adresa_trvala.adresa_pk = abc_pripad.adresa_pk_trvala)
                     ->  Index Scan using kontakt_pkey on kontakt ko  (cost=0.00..0.34 rows=1 width=4)
                           Index Cond: (ko.kontakt_pk = op.kontakt_pk)
               ->  Index Scan using kontakt_verze_kontakt_pk_idx on kontakt_verze kov  (cost=0.00..0.92 rows=9 width=8)
                     Index Cond: (kov.kontakt_pk = ko.kontakt_pk)
         ->  Hash  (cost=5.04..5.04 rows=204 width=4)
               ->  Seq Scan on uzivatel uz_ko  (cost=0.00..5.04 rows=204 width=4)
   ->  Hash  (cost=1.04..1.04 rows=4 width=4)
         ->  Seq Scan on abc_ciselnik abc_ciselnik_abc_pripad_produkt  (cost=0.00..1.04 rows=4 width=4)
(45 rows)