Postgres 10

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 TABLE test AS
SELECT date '2018-01-01' + g%365 AS d  -- all dates in 2018
FROM   generate_series(1,500000) g;

SELECT 500000

SELECT count(*), min(d), max(d) FROM test;  -- just to populate the cache

count
min
max

500000
2018-01-01
2018-12-31

SELECT 1

CREATE FUNCTION f_mmdd_tc_v(date) RETURNS int LANGUAGE sql VOLATILE  AS $$SELECT to_char($1, 'MMDD')::int$$;
CREATE FUNCTION f_mmdd_tc_s(date) RETURNS int LANGUAGE sql STABLE    AS $$SELECT to_char($1, 'MMDD')::int$$;
CREATE FUNCTION f_mmdd_tc_i(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT to_char($1, 'MMDD')::int$$;  -- cannot be inlined!

CREATE FUNCTION f_mmdd_xi_v(date) RETURNS int LANGUAGE sql VOLATILE  AS $$SELECT EXTRACT(month FROM $1)::int * 100 + EXTRACT(day FROM $1)::int$$;
CREATE FUNCTION f_mmdd_xi_i(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT EXTRACT(month FROM $1)::int * 100 + EXTRACT(day FROM $1)::int$$;

CREATE FUNCTION f_mmdd_xx_v(date) RETURNS int LANGUAGE sql VOLATILE  AS $$SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int$$;
CREATE FUNCTION f_mmdd_xx_i(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int$$;

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_tc_v(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..13499.30 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.453 ms

Execution time: 450.698 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_tc_s(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..13499.30 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.090 ms

Execution time: 454.797 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_tc_i(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..148934.90 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.086 ms

Execution time: 2524.365 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_xi_v(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..19142.45 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 1.122 ms

Execution time: 196.706 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_xi_i(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..19142.45 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.162 ms

Execution time: 193.650 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_xx_v(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..17731.66 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.146 ms

Execution time: 181.619 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_mmdd_xx_i(d) FROM test;

QUERY PLAN

Seq Scan on test  (cost=0.00..17731.66 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.145 ms

Execution time: 179.849 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT to_char(d, 'MMDD')::int FROM test; 

QUERY PLAN

Seq Scan on test  (cost=0.00..13499.30 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.047 ms

Execution time: 443.831 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT EXTRACT(month FROM d)::int * 100 + EXTRACT(day FROM d)::int FROM test; 

QUERY PLAN

Seq Scan on test  (cost=0.00..19142.45 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.093 ms

Execution time: 191.012 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT (EXTRACT(month FROM d) * 100 + EXTRACT(day FROM d))::int FROM test; 

QUERY PLAN

Seq Scan on test  (cost=0.00..17731.66 rows=564315 width=4) (actual rows=500000 loops=1)

Planning time: 0.097 ms

Execution time: 179.731 ms

EXPLAIN