Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create function xx (in a integer, in b integer) returns integer
language sql
as $$
select a/b from pg_sleep(1);
$$
select now();

now

2020-12-08 17:52:28.281885+00

-- At this case b is checked before xx() is called
SELECT xx( a, b )
  FROM ( values( 1,0 ), (2, 2), (3,3) ) as t (a, b)
 WHERE b <> 0;

xx

1

1

select now();

now

2020-12-08 17:52:30.286102+00

-- Here condition is checked before xx() is called too
SELECT *
  FROM ( values( 1, 0 ), (2, 2), (3,3) ) as t (a, b)
  LEFT JOIN xx ( a, b ) on false

a
b
xx

1
0
null

2
2
null

3
3
null

select now();

now

2020-12-08 17:52:30.286676+00

-- NOTICE: Here we spent time to caclulating result of xx( 1,1 ), 
-- despite on we will throw away result
-- -1 sec of perfomance for each function call. 
-- But when function call take more time then more time will be wasted by useless function call
SELECT *
  FROM ( values( 1,1 ), (2, 2), (3,3) ) as t (a, b)
  LEFT JOIN xx ( a, b ) on b <> 1

a
b
xx

1
1
null

2
2
1

3
3
1

select now();

now

2020-12-08 17:52:33.296075+00

-- Even in some circumstances query will fail at all 
-- despite on we are trying to filter out bad values
SELECT *
  FROM ( values( 1,0 ), (2, 2), (3,3) ) as t (a, b)
  LEFT JOIN xx ( a, b ) on b <> 0
ERROR:  division by zero
CONTEXT:  SQL function "xx" statement 1

select now();

now

2020-12-08 17:52:34.305121+00

a	b	xx
1	0	null
2	2	null
3	3	null