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 source_table(
    unixdatetime INTEGER NOT NULL,
    extra INTEGER NOT NULL,
    gausesummaryid INTEGER NOT NULL,
    value NUMERIC NOT NULL
);
INSERT INTO source_table(unixdatetime, extra, gausesummaryid, value)
VALUES(1546300800,1,187923,1.5)
    , (1546387200,2,187923,1.5)
    , (1546473600,3,187923,1.5)
    , (1546560000,4,187923,1.75)
    , (1546646400,5,187923,1.75)
    , (1546732800,6,187923,1.75)
    , (1546819200,7,187923,1.75)
    , (1546905600,8,187923,1.5)
    , (1546992000,9,187923,1.5)
    , (1547078400,0,187923,1.5)
    , (1547164800,1,187923,1.5)
    , (1547337600,2,187924,200)
    , (1547424000,3,187924,200)
    , (1547510400,4,187924,200)
    , (1547596800,5,187924,200)
    , (1547683200,6,187924,200)
    , (1547769600,7,187924,200)
    , (1547856000,8,187924,200)
    , (1547942400,9,187924,200)
    , (1548028800,0,187924,200)
    , (1548115200,1,187924,200)
    , (1548201600,2,187924,200)
    , (1548288000,3,187924,200)
    , (1546300800,2,187926,120)
    , (1546387200,2,187926,120)
    , (1546473600,3,187926,120)
    , (1546560000,4,187926,110)
    , (1546646400,5,187926,110)
    , (1546732800,6,187926,110)
    , (1546819200,7,187926,110)
    , (1546905600,8,187926,115)
    , (1546992000,9,187926,115)
    , (1547078400,0,187926,115)
    , (1547942400,9,187927,100)
    , (1548028800,0,187927,100)
    , (1548115200,1,187927,100)
    , (1548201600,2,187927,100)
    , (1548288000,3,187927,100);
38 rows affected

CREATE TABLE category_table(
    id INTEGER NOT NULL,
    name VARCHAR NOT NULL
);
INSERT INTO category_table(id, name)
VALUES (187923, 'trace1')
     , (187924, 'trace2')
     , (187926, 'trace3')
     , (187927, 'trace4');
4 rows affected

SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, extra, category_table.name, value
   FROM  source_table
   JOIN  category_table ON source_table.gausesummaryid = category_table.id
   WHERE extra = 1
   ORDER BY 1
   $$
 , $$
   SELECT name FROM category_table
   $$
) final_result (unixdatetime INTEGER
                 , extra INTEGER
                 , trace1 NUMERIC
                 , trace2 NUMERIC
                 , trace3 NUMERIC
                 , trace4 NUMERIC)


unixdatetime
extra
trace1
trace2
trace3
trace4


1546300800
1
1.5
null
null
null

1547164800
1
1.5
null
null
null

1548115200
1
null
200
null
100


SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, extra, category_table.name, value
   FROM  source_table
   JOIN  category_table ON source_table.gausesummaryid = category_table.id
   WHERE extra = 2
   ORDER BY 1
   $$
 , $$
   SELECT name FROM category_table
   $$
) final_result (unixdatetime INTEGER
                 , extra INTEGER
                 , trace1 NUMERIC
                 , trace2 NUMERIC
                 , trace3 NUMERIC
                 , trace4 NUMERIC)


unixdatetime
extra
trace1
trace2
trace3
trace4


1546300800
2
null
null
120
null

1546387200
2
1.5
null
120
null

1547337600
2
null
200
null
null

1548201600
2
null
200
null
100


SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, extra, category_table.name, value
   FROM  source_table
   JOIN  category_table ON source_table.gausesummaryid = category_table.id
   WHERE extra IN(1, 2)
   ORDER BY 1
   $$
 , $$
   SELECT name FROM category_table
   $$
) final_result (unixdatetime INTEGER
                 , extra INTEGER
                 , trace1 NUMERIC
                 , trace2 NUMERIC
                 , trace3 NUMERIC
                 , trace4 NUMERIC)


unixdatetime
extra
trace1
trace2
trace3
trace4


1546300800
1
1.5
null
120
null

1546387200
2
1.5
null
120
null

1547164800
1
1.5
null
null
null

1547337600
2
null
200
null
null

1548115200
1
null
200
null
100

1548201600
2
null
200
null
100


SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, extra, category_table.name, value
   FROM  source_table
   JOIN  category_table ON source_table.gausesummaryid = category_table.id
   WHERE extra IN(1, 2)
   ORDER BY 1, (extra <> 1)
   $$
 , $$
   SELECT name FROM category_table
   $$
) final_result (unixdatetime INTEGER
                 , extra INTEGER
                 , trace1 NUMERIC
                 , trace2 NUMERIC
                 , trace3 NUMERIC
                 , trace4 NUMERIC)


unixdatetime
extra
trace1
trace2
trace3
trace4


1546300800
1
1.5
null
120
null

1546387200
2
1.5
null
120
null

1547164800
1
1.5
null
null
null

1547337600
2
null
200
null
null

1548115200
1
null
200
null
100

1548201600
2
null
200
null
100


SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, extra, category_table.name, value
   FROM  source_table
   JOIN  category_table ON source_table.gausesummaryid = category_table.id
   WHERE extra IN(1, 2)
   ORDER BY 1, (extra <> 2)
   $$
 , $$
   SELECT name FROM category_table
   $$
) final_result (unixdatetime INTEGER
                 , extra INTEGER
                 , trace1 NUMERIC
                 , trace2 NUMERIC
                 , trace3 NUMERIC
                 , trace4 NUMERIC)


unixdatetime
extra
trace1
trace2
trace3
trace4


1546300800
2
1.5
null
120
null

1546387200
2
1.5
null
120
null

1547164800
1
1.5
null
null
null

1547337600
2
null
200
null
null

1548115200
1
null
200
null
100

1548201600
2
null
200
null
100
unixdatetime	extra	trace1	trace2	trace3	trace4
1546300800	1	1.5	null	null	null
1547164800	1	1.5	null	null	null
1548115200	1	null	200	null	100