Postgres 9.6

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 
        EXTRACT(epoch FROM start::time) start_time,
        EXTRACT(epoch FROM "end"::time) end_time,
        EXTRACT(epoch FROM start::date) start_date,
        EXTRACT(epoch FROM "end"::date) end_date
    FROM (
        SELECT
            '2016-01-01 10:00:00'::timestamp as start,
            '2017-12-31 18:00:00'::timestamp as end
    ) s


start_time
end_time
start_date
end_date


36000
64800
1451606400
1514678400



SELECT 1


-- one value

WITH timestamps AS (
    
    SELECT 
        EXTRACT(epoch FROM start::time) start_time,
        EXTRACT(epoch FROM "end"::time) end_time,
        EXTRACT(epoch FROM start::date) start_date,
        EXTRACT(epoch FROM "end"::date) end_date
    FROM (
        SELECT
            '2016-01-01 10:00:00'::timestamp as start,
            '2017-12-31 18:00:00'::timestamp as end
    ) s
)

SELECT 
    to_timestamp(
        random() * (ts.end_date - ts.start_date) + ts.start_date 
    )::date + 
    to_timestamp(
        random() * (ts.end_time - ts.start_time) + ts.start_time 
    )::time 
FROM timestamps ts


?column?


2017-03-31 16:10:00.207111



SELECT 1


-- more values

WITH timestamps AS (
    
    SELECT 
        EXTRACT(epoch FROM start::time) start_time,
        EXTRACT(epoch FROM "end"::time) end_time,
        EXTRACT(epoch FROM start::date) start_date,
        EXTRACT(epoch FROM "end"::date) end_date
    FROM (
        SELECT
            '2016-01-01 10:00:00'::timestamp as start,
            '2017-12-31 18:00:00'::timestamp as end
    ) s
)

SELECT 
    to_timestamp(
        random() * (ts.end_date - ts.start_date) + ts.start_date 
    )::date + 
    to_timestamp(
        random() * (ts.end_time - ts.start_time) + ts.start_time 
    )::time,
    generate_series(1,10)
FROM timestamps ts


?column?
generate_series


2016-06-15 14:56:48.877815
1

2017-02-25 17:34:31.730679
2

2017-03-25 13:09:47.078302
3

2017-06-08 11:47:00.309171
4

2017-10-21 13:25:54.168624
5

2017-03-10 17:59:04.304131
6

2017-10-04 14:59:03.826
7

2016-03-26 12:48:19.655955
8

2017-07-15 12:54:33.271336
9

2016-12-21 17:31:52.327735
10



SELECT 10

?column?	generate_series
2016-06-15 14:56:48.877815	1
2017-02-25 17:34:31.730679	2
2017-03-25 13:09:47.078302	3
2017-06-08 11:47:00.309171	4
2017-10-21 13:25:54.168624	5
2017-03-10 17:59:04.304131	6
2017-10-04 14:59:03.826	7
2016-03-26 12:48:19.655955	8
2017-07-15 12:54:33.271336	9
2016-12-21 17:31:52.327735	10