--For the further use this function will just serve an example for an expensive operation. It is not important at all that it's a function, it just makes things more readable.
--We obviously don't use that particular function, but the project I extracted this example from has an expensive function.
--It might be worth to note that this function is (despite the random()-call) indeed deterministic and furthermore injective. In the original example I striped down the function wasn't strictly injective.
create or replace function expensive_func (id int) returns int[] 
as
'select array_agg(ent) r from (select unnest(arr) ent from (select array(select i from (select generate_series(id-5,id) i) foo order by random()) arr) bar order by ent) foo;'
language sql immutable; --Costs of several hundred would probably be more appropriate, but it doesn't effect the plans.



--These columns are (besides id and a) nearly independent.
create table t as select id, case when id%11>8 then 100000 else id end a, id%23=0 b, id%3=0 c, id%100=0 d from (select generate_series(1,1000000) id) foo;
create table u as select id, a, b, c, d from t where c;

create table foo100 as select generate_series(1,100) o;
create table foo50000 as select generate_series(1,10000) o;
--We don't need these tables necessarily - just inlining these expressions would do fine (since both differ only by one order of magnitude from the estimate).
--But it will make the plans produce much more reasonable estimates.

create unique index on t (id);
create unique index on t (expensive_func(id));
create index on t (d);
create index on t (c) where b;
create index on t (a) where not d;
create unique index on u (id);
create index on u (a) where a < 100000;

vacuum analyze t;
vacuum analyze u;
vacuum analyze foo100;
vacuum analyze foo50000;



--first case

--Planning time: 0.225 ms
--Execution time: 54.529 ms
explain (analyse, buffers)
select count(*) from t t0 where exists (select null from t where t.id=100000 and t0.a=t.a) and b and c and not d;

--Planning time: 0.081 ms + 0.133 ms = 0.214 ms
--Execution time: 0.077 ms + 15.264 ms = 15.341 ms
explain (analyse, buffers)
select a from t where t.id=100000;
explain (analyse, buffers)
select count(*) from t where a=100000 and b and c and not d;



--second case

--Planning time: 0.132 ms
--Execution time: 298.973 ms
explain (analyse, buffers)
select id from t 
where a in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);

--Planning time: 0.174 ms
--Execution time: 158.666 ms
explain (analyse, buffers)
select id from t
where exists (select null from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) foo (fooa) 
		where a=fooa);

--I guess the limitations of creating a constant table is clear, just in case it's not:
--Planning time: 0.142 ms
--Execution time: 50.550 ms
explain (analyse, buffers)
select id from u
where exists (select null from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)) foo (fooa) 
		where a=fooa);

--Planning time: 0.197 ms
--Execution time: 0.066 ms
explain (analyse, buffers)
select id from u
where a in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30);




--third case

--Planning time: 0.448 ms
--Execution time: 9295.156 ms
explain (analyse, buffers)
select id from t where exists (select null from u
		where t.id=u.id
			and exists (select null from foo100 where expensive_func(o)=expensive_func(u.id)))
	and not d;

--Planning time: 0.556 ms
--Execution time: 8.767 ms
explain (analyse, buffers)
select id from t where exists (select null from u
		where t.id=u.id
			and exists (select null from foo100 where expensive_func(o)=expensive_func(t.id)))
	and not d;

--Planning time: 0.443 ms
--Execution time: 313.997 ms
explain (analyse, buffers)
select id from t
where exists (select null from u
		where t.id=u.id
			and exists (select null from foo50000 where expensive_func(o)=expensive_func(u.id)))
	and d;

--Planning time: 0.678 ms
--Execution time: 602.052 ms
explain (analyse, buffers)
select id from t
where exists (select null from u
		where t.id=u.id
			and exists (select null from foo50000 where expensive_func(o)=expensive_func(t.id)))
	and d;
--I don't think this plan is optimal, but this task is non the less much harder than the one above.