Postgres Pro
Downloads
Home   >   mailing lists

Strange left join problems in 8.1 - Mailing list pgsql-hackers

From Mario Weilguni
Subject Strange left join problems in 8.1
Date
Msg-id FA095C015271B64E99B197937712FD020E4B05EC@freedom.grz.icomedias.com
Whole thread Raw
Responses Re: Strange left join problems in 8.1
List pgsql-hackers
Tree view
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
 
 

pgsql-hackers by date:

Previous
From: Robert Treat
Date:
Subject: Re: Reducing relation locking overhead
Next
From: Greg Stark
Date:
Subject: Re: Reducing relation locking overhead