Postgres 12

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(6300800,1,923,1.5)
    , (6387200,2,923,1.5)
    , (6473600,3,923,1.5)
    , (6560000,4,923,1.75)
    , (6646400,5,923,1.75)
    , (6732800,6,923,1.75)
    , (6819200,7,923,1.75)
    , (6905600,8,923,1.5)
    , (6992000,9,923,1.5)
    , (7078400,0,923,1.5)
    , (7164800,1,923,1.5)
    , (7337600,2,924,200)
    , (7424000,3,924,200)
    , (7510400,4,924,200)
    , (7596800,5,924,200)
    , (7683200,6,924,200)
    , (7769600,7,924,200)
    , (7856000,8,924,200)
    , (7942400,9,924,200)
    , (8028800,0,924,200)
    , (8115200,1,924,200)
    , (8201600,2,924,200)
    , (8288000,3,924,200)
    , (6300800,2,926,120)
    , (6387200,2,926,120)
    , (6473600,3,926,120)
    , (6560000,4,926,110)
    , (6646400,5,926,110)
    , (6732800,6,926,110)
    , (6819200,7,926,110)
    , (6905600,8,926,115)
    , (6992000,9,926,115)
    , (7078400,0,926,115)
    , (7942400,9,927,100)
    , (8028800,0,927,100)
    , (8115200,1,927,100)
    , (8201600,2,927,100)
    , (8288000,3,927,100);

CREATE TABLE category_table(
    id INTEGER NOT NULL,
    name VARCHAR NOT NULL
);
INSERT INTO category_table(id, name)
VALUES (923, 'trace1')
     , (924, 'trace2')
     , (926, 'trace3')
     , (927, 'trace4');
     
38 rows affected

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


6300800
1
1.5
null
null
null

7164800
1
1.5
null
null
null

8115200
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


6300800
2
null
null
120
null

6387200
2
1.5
null
120
null

7337600
2
null
200
null
null

8201600
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


6300800
1
1.5
null
120
null

6387200
2
1.5
null
120
null

7164800
1
1.5
null
null
null

7337600
2
null
200
null
null

8115200
1
null
200
null
100

8201600
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


6300800
1
1.5
null
120
null

6387200
2
1.5
null
120
null

7164800
1
1.5
null
null
null

7337600
2
null
200
null
null

8115200
1
null
200
null
100

8201600
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


6300800
2
1.5
null
120
null

6387200
2
1.5
null
120
null

7164800
1
1.5
null
null
null

7337600
2
null
200
null
null

8115200
1
null
200
null
100

8201600
2
null
200
null
100


-- to return all extra values
SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, x.extras, c.name, s.value
   FROM  (
      SELECT unixdatetime, array_agg(extra) AS extras
      FROM  (
         SELECT DISTINCT unixdatetime, extra
         FROM   source_table
         WHERE  extra IN (1, 2)     -- condition moves here
         ORDER  BY unixdatetime, extra
         ) sub
      GROUP  BY 1
      ) x
   JOIN   source_table s USING (unixdatetime)
   JOIN   category_table c ON c.id = s.gausesummaryid
   ORDER  BY 1
   $$
 , $$SELECT unnest('{trace1,trace2,trace3,trace4}'::text[])$$
) AS final_result (unixdatetime int
                 , extras int[]
                 , trace1 numeric
                 , trace2 numeric
                 , trace3 numeric
                 , trace4 numeric);
                 


unixdatetime
extras
trace1
trace2
trace3
trace4


6300800
{1,2}
1.5
null
120
null

6387200
{2}
1.5
null
120
null

7164800
{1}
1.5
null
null
null

7337600
{2}
null
200
null
null

8115200
{1}
null
200
null
100

8201600
{2}
null
200
null
100


-- the same, a bit faster, yet
SELECT * 
FROM   crosstab(
   $$
   SELECT unixdatetime, x.extras, s.gausesummaryid, s.value
   FROM  (
      SELECT unixdatetime, array_agg(extra) AS extras
      FROM  (
         SELECT DISTINCT unixdatetime, extra
         FROM   source_table
         WHERE  extra IN (1, 2)     -- condition moves here
         ORDER  BY unixdatetime, extra
         ) sub
      GROUP  BY 1
      ) x
   JOIN   source_table s USING (unixdatetime)
   ORDER  BY 1
   $$
,  $$SELECT unnest('{923,924,926,927}'::int[])$$ -- matching IDs directly
) AS final_result (unixdatetime int
                 , extras int[]
                 , trace1 numeric
                 , trace2 numeric
                 , trace3 numeric
                 , trace4 numeric);
                 


unixdatetime
extras
trace1
trace2
trace3
trace4


6300800
{1,2}
1.5
null
120
null

6387200
{2}
1.5
null
120
null

7164800
{1}
1.5
null
null
null

7337600
{2}
null
200
null
null

8115200
{1}
null
200
null
100

8201600
{2}
null
200
null
100
unixdatetime	extra	trace1	trace2	trace3	trace4
6300800	1	1.5	null	null	null
7164800	1	1.5	null	null	null
8115200	1	null	200	null	100
unixdatetime	extras	trace1	trace2	trace3	trace4
6300800	{1,2}	1.5	null	120	null
6387200	{2}	1.5	null	120	null
7164800	{1}	1.5	null	null	null
7337600	{2}	null	200	null	null
8115200	{1}	null	200	null	100
8201600	{2}	null	200	null	100