[BUGS] BUG #14506: The execution plan with Inner Join is different whenusing clause table.column is not null

The following bug has been logged on the website:

Bug reference:      14506
Logged by:          Gerdan Santos
Email address:      gerdan@gmail.com
PostgreSQL version: 9.6.1
Operating system:   Centos 6.7
Description:

I've this situation above. Table b has 400 rows with null in the column b.

explain analyze select * from a inner join b on (b.b = a.a);

"Merge Join  (cost=0.55..65.30 rows=599 width=16) (actual time=0.030..1.173
rows=599 loops=1)"
"  Merge Cond: (a.a = b.b)"
"  ->  Index Scan using a_pkey on a  (cost=0.28..35.27 rows=1000 width=8)
(actual time=0.014..0.364 rows=1000 loops=1)"
"  ->  Index Scan using in01 on b  (cost=0.28..33.27 rows=1000 width=8)
(actual time=0.012..0.249 rows=600 loops=1)"
"Total runtime: 1.248 ms"

My question is: Why the planner isn't removing the null rows during the 
scan of table b?

How to replicate the bug.

--Create the tables a and b

CREATE TABLE public.a
(
    a integer NOT NULL,
    b integer,
    CONSTRAINT a_pkey PRIMARY KEY (a)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.a
    OWNER to postgres;


    
    
CREATE TABLE public.b
(
    a integer NOT NULL,
    b integer,
    CONSTRAINT b_pkey PRIMARY KEY (a),
    CONSTRAINT fk_b_b_a_a FOREIGN KEY (b)
        REFERENCES public.a (a) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.b
    OWNER to postgres;


-- Insert the data on table     
insert into a                         
select s.a, s.a * 0.2
from generate_series(1, 1000) as s(a);

insert into b                         
select a, a from a;


--UPDATE some rows to null
update b 
set b = null
where a between 100 and 500;

analyze a;
analyze b;


-- Execute de analyse of querys ATTENTION: Second query in my understanding
this query executes with better execution plan discarding the lines where
the join can not be null by definition of INNER JOIN


explain analyze verbose select *
from a
inner join b on (a.a = b.b);


+---------------------------------------------------------------------------------------------------------------------+
|                                                     QUERY PLAN
                                         |

+---------------------------------------------------------------------------------------------------------------------+
| Hash Join  (cost=27.50..53.24 rows=1000 width=16) (actual
time=2.139..4.593 rows=599 loops=1)                       |
|   Output: a.a, a.b, b.a, b.b
                                         |
|   Hash Cond: (b.b = a.a)
                                         |
|   ->  Seq Scan on public.b  (cost=0.00..16.00 rows=1000 width=8) (actual
time=0.013..0.788 rows=1000 loops=1)       |
|         Output: b.a, b.b
                                         |
|   ->  Hash  (cost=15.00..15.00 rows=1000 width=8) (actual
time=2.104..2.104 rows=1000 loops=1)                      |
|         Output: a.a, a.b
                                         |
|         Buckets: 1024  Batches: 1  Memory Usage: 32kB
                                         |
|         ->  Seq Scan on public.a  (cost=0.00..15.00 rows=1000 width=8)
(actual time=0.011..0.972 rows=1000 loops=1) |
|               Output: a.a, a.b
                                         |
| Planning time: 0.566 ms
                                         |
| Execution time: 5.096 ms
                                         |

+---------------------------------------------------------------------------------------------------------------------+



explain analyze verbose select *
from a
inner join b on (a.a = b.b)
where b.b is not null;

+-------------------------------------------------------------------------------------------------------------------+
|                                                    QUERY PLAN
                                       |
+-------------------------------------------------------------------------------------------------------------------+
| Hash Join  (cost=23.49..47.08 rows=599 width=16) (actual time=0.434..1.314
rows=599 loops=1)                      |
|   Output: a.a, a.b, b.a, b.b
                                       |
|   Hash Cond: (a.a = b.b)
                                       |
|   ->  Seq Scan on public.a  (cost=0.00..15.00 rows=1000 width=8) (actual
time=0.007..0.344 rows=1000 loops=1)     |
|         Output: a.a, a.b
                                       |
|   ->  Hash  (cost=16.00..16.00 rows=599 width=8) (actual time=0.420..0.420
rows=599 loops=1)                      |
|         Output: b.a, b.b
                                       |
|         Buckets: 1024  Batches: 1  Memory Usage: 21kB
                                       |
|         ->  Seq Scan on public.b  (cost=0.00..16.00 rows=599 width=8)
(actual time=0.003..0.220 rows=599 loops=1) |
|               Output: b.a, b.b
                                       |
|               Filter: (b.b IS NOT NULL)
                                       |
|               Rows Removed by Filter: 401
                                       |
| Planning time: 0.247 ms
                                       |
| Execution time: 1.471 ms
                                       |
+-------------------------------------------------------------------------------------------------------------------+





--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs