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?.
SELECT oid::regprocedure AS function_signature
, prorettype::regtype AS return_type
FROM pg_proc
where proname = 'generate_series';
| function_signature | return_type |
|---|---|
| generate_series(integer,integer,integer) | integer |
| generate_series(integer,integer) | integer |
| generate_series(bigint,bigint,bigint) | bigint |
| generate_series(bigint,bigint) | bigint |
| generate_series(numeric,numeric,numeric) | numeric |
| generate_series(numeric,numeric) | numeric |
| generate_series(timestamp without time zone,timestamp without time zone,interval) | timestamp without time zone |
| generate_series(timestamp with time zone,timestamp with time zone,interval) | timestamp with time zone |
-- note the additional cast to timestamptz!
EXPLAIN (ANALYZE, VERBOSE)
SELECT day::date
FROM generate_series(date '2004-03-07'
, date '2004-08-16'
, interval '1 day') day;
| QUERY PLAN |
|---|
| Function Scan on pg_catalog.generate_series day (cost=0.01..12.51 rows=1000 width=4) (actual time=0.122..0.183 rows=163 loops=1) |
| Output: (day)::date |
| Function Call: generate_series(('2004-03-07'::date)::timestamp with time zone, ('2004-08-16'::date)::timestamp with time zone, '1 day'::interval) |
| Planning time: 0.031 ms |
| Execution time: 0.218 ms |
EXPLAIN (ANALYZE, VERBOSE)
SELECT day::date
FROM generate_series(timestamp '2004-03-07'
, timestamp '2004-08-16'
, interval '1 day') day;
| QUERY PLAN |
|---|
| Function Scan on pg_catalog.generate_series day (cost=0.00..12.50 rows=1000 width=4) (actual time=0.051..0.087 rows=163 loops=1) |
| Output: (day)::date |
| Function Call: generate_series('2004-03-07 00:00:00'::timestamp without time zone, '2004-08-16 00:00:00'::timestamp without time zone, '1 day'::interval) |
| Planning time: 0.024 ms |
| Execution time: 0.116 ms |
-- equivalent
EXPLAIN (ANALYZE, TIMING OFF)
SELECT day::date
FROM generate_series(timestamp '2004-03-07', '2004-08-16', '1 day') day;
| QUERY PLAN |
|---|
| Function Scan on generate_series day (cost=0.00..12.50 rows=1000 width=4) (actual rows=163 loops=1) |
| Planning time: 0.023 ms |
| Execution time: 0.098 ms |
-- faster equivalent
EXPLAIN (ANALYZE, TIMING OFF)
SELECT generate_series(timestamp '2004-03-07', '2004-08-16', '1 day')::date AS day;
| QUERY PLAN |
|---|
| Result (cost=0.00..20.02 rows=1000 width=4) (actual rows=163 loops=1) |
| -> ProjectSet (cost=0.00..5.02 rows=1000 width=8) (actual rows=163 loops=1) |
| -> Result (cost=0.00..0.01 rows=1 width=0) (actual rows=1 loops=1) |
| Planning time: 0.025 ms |
| Execution time: 0.085 ms |