Postgres 11

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 environment (env_id int, placem_id int,humd int,wind int)

CREATE TABLE


insert into environment
select 104,4,48,119 union all
select 68,9,39,141

INSERT 0 2


create table placement (placem_id int, loc_id int, description text, date date)

CREATE TABLE


insert into placement
select 4, 64, 'description_1', '2019-03-12T20:40:35.967Z'::date union all
select 9, 5, 'description_2', '2019-03-12T20:56:51.319Z'::date

INSERT 0 2


create table location (loc_id int, log float, lat float, address text)

CREATE TABLE


insert into location
select 64, 13.3986, 52.5547, 'Bosebrucke Einkauf' union all
select 5, 21.150122, -6.607044, 'Charlotte Court'

INSERT 0 2


select
    json_agg(to_json(d))
from (
    select
        e.humd as "relativeHumidity",
        e.wind as "windSpeed",
        l.address,
        json_build_object(
            'coordinates',
            json_build_array(l.log, l.lat)
        ) as location
    from environment as e
        inner join placement as p on
            p.placem_id = e.placem_id
        inner join location as l on
            l.loc_id = p.loc_id
) as d


json_agg


[{"relativeHumidity":48,"windSpeed":119,"address":"Bosebrucke Einkauf","location":{"coordinates" : [13.3986, 52.5547]}}, {"relativeHumidity":39,"windSpeed":141,"address":"Charlotte Court","location":{"coordinates" : [21.150122, -6.607044]}}]



SELECT 1