Postgresql 9.2 prepare query test

pgsql-use-index-on-coalesce-arg.sql

drop table if exists parent cascade;
drop table if exists child cascade;
deallocate all;

create table parent (id serial primary key, val int unique);
create table child (id serial primary key, val int references parent(val));

insert into parent (val)
select case when i % 11 <> 0 then i else null end
from generate_series(1,10000) i;

insert into child (val)
select i
from generate_series(1,10000) i
where i % 11 <> 0 and i % 13 <> 0;

create index on parent(val);
create index on child(val);
  
vacuum analyze parent;
vacuum analyze child;

prepare query(int, int) as
select *
from parent
left join child on child.val = parent.val
where (parent.id = $1 or $1 is null)
and   (child.id = $2 or $2 is null);

explain analyze execute query(2, null);
explain analyze execute query(null, 2);

---

# \i ./test.sql
DROP TABLE
DROP TABLE
DEALLOCATE ALL
CREATE TABLE
CREATE TABLE
INSERT 0 10000
INSERT 0 8391
CREATE INDEX
CREATE INDEX
VACUUM
VACUUM
PREPARE
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..16.55 rows=1 width=16) (actual time=0.017..0.019 rows=1 loops=1)
   ->  Index Scan using parent_pkey on parent  (cost=0.00..8.27 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)
         Index Cond: (id = 2)
   ->  Index Scan using child_val_idx on child  (cost=0.00..8.27 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
         Index Cond: (val = parent.val)
 Total runtime: 0.053 ms
(6 rows)

                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..16.55 rows=1 width=16) (actual time=0.014..0.016 rows=1 loops=1)
   ->  Index Scan using child_pkey on child  (cost=0.00..8.27 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)
         Index Cond: (id = 2)
   ->  Index Scan using parent_val_idx on parent  (cost=0.00..8.27 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
         Index Cond: (val = child.val)
 Total runtime: 0.041 ms
(6 rows)