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,50000) g;

SELECT 50000

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

count
min
max

50000
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_i(date) RETURNS int LANGUAGE sql IMMUTABLE AS $$SELECT to_char($1, 'MMDD')::int$$;  -- cannot be inlined!

CREATE FUNCTION

CREATE FUNCTION

EXPLAIN (ANALYZE, VERBOSE) SELECT f_mmdd_tc_v(d) FROM test;

QUERY PLAN

Seq Scan on public.test  (cost=0.00..1354.20 rows=56610 width=4) (actual time=8.601..50.223 rows=50000 loops=1)

  Output: (to_char((d)::timestamp with time zone, 'MMDD'::text))::integer

Planning time: 0.550 ms

Execution time: 52.148 ms

EXPLAIN

EXPLAIN (ANALYZE, VERBOSE) SELECT f_mmdd_tc_i(d) FROM test;

QUERY PLAN

Seq Scan on public.test  (cost=0.00..14940.60 rows=56610 width=4) (actual time=0.068..246.538 rows=50000 loops=1)

  Output: f_mmdd_tc_i(d)

Planning time: 0.085 ms

Execution time: 249.778 ms

EXPLAIN