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 my_table (
    id serial primary key,
    time_stamp timestamp without time zone not null,
    value double precision not null
);
insert into my_table (time_stamp, value)
select '2018-06-08 10:00'::timestamp+ '3m'::interval* i, i
from generate_series(1, 10) i;
10 rows affected

select *
from my_table;


id
time_stamp
value


1
2018-06-08 10:03:00
1

2
2018-06-08 10:06:00
2

3
2018-06-08 10:09:00
3

4
2018-06-08 10:12:00
4

5
2018-06-08 10:15:00
5

6
2018-06-08 10:18:00
6

7
2018-06-08 10:21:00
7

8
2018-06-08 10:24:00
8

9
2018-06-08 10:27:00
9

10
2018-06-08 10:30:00
10


create or replace function first_agg(anyelement, anyelement)
returns anyelement language sql immutable strict
as $$ select $1; $$;
create or replace function last_agg(anyelement, anyelement)
returns anyelement language sql immutable strict
as $$ select $2; $$;
create aggregate first(anyelement) (
	sfunc = first_agg,
	stype = anyelement
);
create aggregate last(anyelement) (
	sfunc = last_agg,
	stype = anyelement
);
SELECT
    first(value order by time_stamp) as first_value,
    last(value order by time_stamp) as last_value,
    CAST(EXTRACT(year FROM time_stamp) AS INTEGER) AS year,
    CAST(EXTRACT(month FROM time_stamp) AS INTEGER) AS month,
    CAST(EXTRACT(day FROM time_stamp) AS INTEGER) AS day,
    CAST(EXTRACT(hour FROM time_stamp) AS INTEGER) AS hour,
    floor(EXTRACT(minute FROM time_stamp) / 15) AS quarter
FROM
    my_table
GROUP BY
    year,
    month,
    day,
    hour,
    quarter
ORDER BY
    year,
    month,
    day,
    hour,
    quarter;


first_value
last_value
year
month
day
hour
quarter


1
4
2018
6
8
10
0

5
9
2018
6
8
10
1

10
10
2018
6
8
10
2

id	time_stamp	value
1	2018-06-08 10:03:00	1
2	2018-06-08 10:06:00	2
3	2018-06-08 10:09:00	3
4	2018-06-08 10:12:00	4
5	2018-06-08 10:15:00	5
6	2018-06-08 10:18:00	6
7	2018-06-08 10:21:00	7
8	2018-06-08 10:24:00	8
9	2018-06-08 10:27:00	9
10	2018-06-08 10:30:00	10