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

"Mario Weilguni" <mario(dot)weilguni(at)icomedias(dot)com> writes:
> It seems like the planner is pulling the last where condition into the
> second left join, evaluating it in wrong order.

Can you try this on 8.1 branch tip? It sounds suspiciously similar to
an already-fixed issue (see reports from Sebastian Bck around
mid-November). You haven't provided a complete test case so I really
can't investigate for myself ...

regards, tom lane

Can you try this on 8.1 branch tip? It sounds suspiciously similar to

>an already-fixed issue (see reports from Sebastian Böck around
>mid-November). You haven't provided a complete test case so I really
>can't investigate for myself ...
>
> regards, tom lane
>
>
Of course I can give it a try, do I need to re-initdb when I use cvs to
checkout 8.1 tip? I don't think so, isn't it?

Best regards,
Mario Weilguni

Mario Weilguni <mweilguni(at)sime(dot)com> writes:
> Of course I can give it a try, do I need to re-initdb when I use cvs to
> checkout 8.1 tip?

No, not if you're on 8.1 final (or anything post-beta4, IIRC). Just be
sure to use the same configure arguments as before (pg_config can help
remind you if you forgot).

regards, tom lane