postgres planner being fooled

example.sql

drop table if exists tst_entry;
drop table if exists tst_child;
drop table if exists tst_parent;

CREATE TABLE tst_parent (
	id int NOT null primary key,
	title text NULL
);

insert into tst_parent
select g.i, 'Parent ' || g.i
from generate_series(1, 10000) g (i);

analyze tst_parent;

CREATE TABLE tst_child (
	id serial primary key,
	parentid int NOT null references tst_parent (id) on delete cascade,
	title text NULL
);

CREATE INDEX idx_tst_child_parent ON tst_child (parentid);

do $$
declare
	p record;
	vCnt int;
begin
	for p in (select id from tst_parent) loop
		vCnt := 1 + floor(random() * 5)::int;
		insert into tst_child (parentid, title)
		select p.id, 'Child ' || p.id || '.' || g.i
		from generate_series(1, vCnt) g(i);
	end loop;
end
$$ language plpgsql;

analyze tst_child;

create table tst_entry (
	id serial primary key,
	parentid int not null references tst_parent (id),
	childid int not null references tst_child (id),
	title text
);

do $$
declare
	vCnt int;
	child record;
begin
	for vParentId in 1 .. 100 loop
		for child in (select id from tst_child where parentid = vParentId) loop 
			vCnt := 1 + floor(random() * 5000);
			insert into tst_entry (parentid, childid, title)
			select vParentId, child.id, 'Entry #' || vParentId || '.' || g.i
			from generate_series(1, vCnt) g(i);
		end loop;
	end loop;
end
$$ language plpgsql;

create index idx_tst_entry_t on tst_entry (parentid, childid, id);

analyze tst_entry;

-- QUERY #1: condition "c.parentid = 3" is actually just duplicated by "w.parentid = 3"

explain (analyze, buffers)
select w.id, c.title, w.title
from tst_entry w
inner join tst_child c on w.childid = c.id
where c.parentid = 3
and w.parentid = 3
order by w.parentid, w.childid, w.id;

/*
QUERY PLAN                                                                                                                                   |
---------------------------------------------------------------------------------------------------------------------------------------------+
Sort  (cost=41.19..41.20 rows=1 width=38) (actual time=3.093..3.502 rows=6249 loops=1)                                                       |
  Sort Key: w.childid, w.id                                                                                                                  |
  Sort Method: quicksort  Memory: 681kB                                                                                                      |
  Buffers: shared hit=81                                                                                                                     |
  ->  Nested Loop  (cost=0.71..41.18 rows=1 width=38) (actual time=0.016..1.879 rows=6249 loops=1)                                           |
        Buffers: shared hit=81                                                                                                               |
        ->  Index Scan using idx_tst_child_parent on tst_child c  (cost=0.29..2.54 rows=3 width=16) (actual time=0.005..0.012 rows=2 loops=1)|
              Index Cond: (parentid = 3)                                                                                                     |
              Buffers: shared hit=4                                                                                                          |
        ->  Index Scan using idx_tst_entry_t on tst_entry w  (cost=0.42..12.67 rows=21 width=26) (actual time=0.006..0.562 rows=3124 loops=2)|
              Index Cond: ((parentid = 3) AND (childid = c.id))                                                                              |
              Buffers: shared hit=77                                                                                                         |
Planning:                                                                                                                                    |
  Buffers: shared hit=4                                                                                                                      |
Planning Time: 0.151 ms                                                                                                                      |
Execution Time: 4.132 ms                                                                                                                     |
*/

-- QUERY #2: no duplication

explain (analyze, buffers)
select w.id, c.title, w.title
from tst_entry w
inner join tst_child c on w.childid = c.id
where w.parentid = 3
order by w.parentid, w.childid, w.id;

/*
QUERY PLAN                                                                                                                                 |
-------------------------------------------------------------------------------------------------------------------------------------------+
Merge Join  (cost=0.71..2207.91 rows=6547 width=38) (actual time=0.038..1.602 rows=6249 loops=1)                                           |
  Merge Cond: (w.childid = c.id)                                                                                                           |
  Buffers: shared hit=76                                                                                                                   |
  ->  Index Scan using idx_tst_entry_t on tst_entry w  (cost=0.42..2117.39 rows=6547 width=26) (actual time=0.027..0.817 rows=6249 loops=1)|
        Index Cond: (parentid = 3)                                                                                                         |
        Buffers: shared hit=73                                                                                                             |
  ->  Index Scan using tst_child_pkey on tst_child c  (cost=0.29..740.97 rows=30132 width=16) (actual time=0.004..0.007 rows=7 loops=1)    |
        Buffers: shared hit=3                                                                                                              |
Planning:                                                                                                                                  |
  Buffers: shared hit=4                                                                                                                    |
Planning Time: 0.216 ms                                                                                                                    |
Execution Time: 1.876 ms                                                                                                                   |
*/