Strange left join problems in 8.1

I've quite interesting results in 8.1, I'm not sure if the queries
itself are beyond SQL specs, but the results are quite interesting:
This is the most simple I found for the query that still has the
problem, the second left join is not really necessary and can be
replaced


Correct result:
select count(1)
from beitraege bei
left join (b_ltk_protokoll blp
left join abw_mailqueue mq on (mq.id =
blp.mq_id) ) on (
blp.bei_id = bei.id
and blp.grund = 'notify_verschickt_frei'
)
where bei.id = 10078101
and coalesce(mq.kz_verschicken,'N')='N';
count
-------
0
(1 row)

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------
Aggregate (cost=10.76..10.77 rows=1 width=0) (actual time=0.209..0.211
rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..10.76 rows=1 width=0) (actual
time=0.201..0.201 rows=0 loops=1)
Join Filter: ("inner".bei_id = "outer".id)
Filter: ((COALESCE("inner".kz_verschicken, 'N'::character
varying))::text = 'N'::text)
-> Index Scan using beitraege_pkey on beitraege bei
(cost=0.00..3.91 rows=1 width=4) (actual time=0.036..0.038 rows=1
loops=1)
Index Cond: (id = 10078101)
-> Nested Loop Left Join (cost=0.00..6.83 rows=1 width=9)
(actual time=0.132..0.144 rows=1 loops=1)
-> Index Scan using b_ltk_protokoll_bei_id_key on
b_ltk_protokoll blp (cost=0.00..3.65 rows=1 width=8) (actual
time=0.105..0.110 rows=1 loops=1)
Index Cond: ((bei_id = 10078101) AND (grund =
'notify_verschickt_frei'::text))
-> Index Scan using abw_mailqueue_pkey on abw_mailqueue
mq (cost=0.00..3.17 rows=1 width=9) (actual time=0.017..0.020 rows=1
loops=1)
Index Cond: (mq.id = "outer".mq_id)
Total runtime: 0.473 ms
(12 rows)

Wrong result, Version 1
select count(1)
from beitraege bei
left join (b_ltk_protokoll blp
left join abw_mailqueue mq on (mq.id =
blp.mq_id) ) on (
blp.bei_id = bei.id
and blp.grund = 'notify_verschickt_frei'
)
where bei.id = 10078101
and (mq.kz_verschicken is null or mq.kz_verschicken = 'N');
count
-------
1
(1 row)
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------
Aggregate (cost=10.76..10.77 rows=1 width=0)
-> Nested Loop Left Join (cost=0.00..10.76 rows=1 width=0)
Join Filter: ("inner".bei_id = "outer".id)
Filter: (("inner".kz_verschicken IS NULL) OR
(("inner".kz_verschicken)::text = 'N'::text))
-> Index Scan using beitraege_pkey on beitraege bei
(cost=0.00..3.91 rows=1 width=4)
Index Cond: (id = 10078101)
-> Nested Loop Left Join (cost=0.00..6.83 rows=1 width=9)
Filter: (("inner".kz_verschicken IS NULL) OR
(("inner".kz_verschicken)::text = 'N'::text))
-> Index Scan using b_ltk_protokoll_bei_id_key on
b_ltk_protokoll blp (cost=0.00..3.65 rows=1 width=8)
Index Cond: ((bei_id = 10078101) AND (grund =
'notify_verschickt_frei'::text))
-> Index Scan using abw_mailqueue_pkey on abw_mailqueue
mq (cost=0.00..3.17 rows=1 width=9)
Index Cond: (mq.id = "outer".mq_id)

Wrong result, Version 2:
select count(1)
from beitraege bei
left join (b_ltk_protokoll blp
left join abw_mailqueue mq on (mq.id =
blp.mq_id) ) on (
blp.bei_id = bei.id
and blp.grund = 'notify_verschickt_frei'
)
where bei.id = 10078101
and (mq.id is null or (mq.id is not null and mq.kz_verschicken =
'N'));
count
-------
1
(1 row)

------------------------------------------------------------------------
------------------------------------------------------------------------
------------------
Aggregate (cost=10.76..10.77 rows=1 width=0) (actual time=0.225..0.226
rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..10.76 rows=1 width=0) (actual
time=0.208..0.212 rows=1 loops=1)
Join Filter: ("inner".bei_id = "outer".id)
Filter: (("inner".id IS NULL) OR (("inner".id IS NOT NULL) AND
(("inner".kz_verschicken)::text = 'N'::text)))
-> Index Scan using beitraege_pkey on beitraege bei
(cost=0.00..3.91 rows=1 width=4) (actual time=0.036..0.038 rows=1
loops=1)
Index Cond: (id = 10078101)
-> Nested Loop Left Join (cost=0.00..6.83 rows=1 width=13)
(actual time=0.161..0.161 rows=0 loops=1)
Filter: (("inner".id IS NULL) OR (("inner".id IS NOT
NULL) AND (("inner".kz_verschicken)::text = 'N'::text)))
-> Index Scan using b_ltk_protokoll_bei_id_key on
b_ltk_protokoll blp (cost=0.00..3.65 rows=1 width=8) (actual
time=0.117..0.121 rows=1 loops=1)
Index Cond: ((bei_id = 10078101) AND (grund =
'notify_verschickt_frei'::text))
-> Index Scan using abw_mailqueue_pkey on abw_mailqueue
mq (cost=0.00..3.17 rows=1 width=9) (actual time=0.016..0.019 rows=1
loops=1)
Index Cond: (mq.id = "outer".mq_id)
Total runtime: 0.511 ms
(13 rows)

This one is better to understand because the second left join is
eliminated:
select count(1)
from beitraege bei
left join (select *
from b_ltk_protokoll blp,
abw_mailqueue mq
where mq.id = blp.mq_id
and blp.grund = 'notify_verschickt_frei'
) as foo on ( foo.bei_id = bei.id )
where bei.id = 10078101
and (foo.kz_verschicken is null or foo.kz_verschicken = 'N');
count
-------
1
(1 row)
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------
Aggregate (cost=10.76..10.77 rows=1 width=0) (actual time=0.239..0.241
rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..10.76 rows=1 width=0) (actual
time=0.221..0.226 rows=1 loops=1)
Join Filter: ("inner".bei_id = "outer".id)
Filter: (("inner".kz_verschicken IS NULL) OR
(("inner".kz_verschicken)::text = 'N'::text))
-> Index Scan using beitraege_pkey on beitraege bei
(cost=0.00..3.91 rows=1 width=4) (actual time=0.036..0.038 rows=1
loops=1)
Index Cond: (id = 10078101)
-> Nested Loop (cost=0.00..6.83 rows=1 width=9) (actual
time=0.175..0.175 rows=0 loops=1)
Join Filter: (("inner".kz_verschicken IS NULL) OR
(("inner".kz_verschicken)::text = 'N'::text))
-> Index Scan using b_ltk_protokoll_bei_id_key on
b_ltk_protokoll blp (cost=0.00..3.65 rows=1 width=8) (actual
time=0.125..0.129 rows=1 loops=1)
Index Cond: ((bei_id = 10078101) AND (grund =
'notify_verschickt_frei'::text))
-> Index Scan using abw_mailqueue_pkey on abw_mailqueue
mq (cost=0.00..3.17 rows=1 width=9) (actual time=0.017..0.020 rows=1
loops=1)
Index Cond: (mq.id = "outer".mq_id)
Total runtime: 0.528 ms
(13 rows)
(same queryplan as above)

Here's the query plan for 8.0, slightly other data, but correct behavior
select count(1)
from beitraege bei
left join (b_ltk_protokoll blp
left join abw_mailqueue mq on (mq.id = blp.mq_id)

) on ( blp.bei_id = bei.id
and blp.grund = 'notify_verschickt_frei'
)
where
bei.id = 10194579
and (mq.kz_verschicken is null or mq.kz_verschicken = 'N');
count
-------
0
(1 row)

Nested Loop Left Join (cost=89.68..93.85 rows=1 width=21) (actual
time=1.574..1.574 rows=0 loops=1)
Join Filter: ("inner".bei_id = "outer".id)
Filter: (("inner".kz_verschicken IS NULL) OR
(("inner".kz_verschicken)::text = 'N'::text))
-> Index Scan using beitraege_pkey on beitraege bei
(cost=0.00..3.68 rows=1 width=4) (actual time=0.029..0.031 rows=1
loops=1)
Index Cond: (id = 10194579)
-> Merge Left Join (cost=89.68..90.03 rows=9 width=21) (actual
time=1.476..1.528 rows=3 loops=1)
Merge Cond: ("outer".mq_id = "inner".id)
-> Sort (cost=68.31..68.33 rows=9 width=8) (actual
time=1.205..1.207 rows=3 loops=1)
Sort Key: blp.mq_id
-> Seq Scan on b_ltk_protokoll blp (cost=0.00..68.16
rows=9 width=8) (actual time=0.115..1.189 rows=3 loops=1)
Filter: (grund = ''notify_verschickt_frei'::text)
-> Sort (cost=21.37..21.52 rows=60 width=17) (actual
time=0.230..0.266 rows=60 loops=1)
Sort Key: mq.id
-> Seq Scan on abw_mailqueue mq (cost=0.00..19.60
rows=60 width=17) (actual time=0.006..0.166 rows=60 loops=1)
Total runtime: 1.702 ms
(15 rows)

It seems like the planner is pulling the last where condition into the
second left join, evaluating it in wrong order.

Any idea what's going wrong here?

Best regards,
Mario Weilguni