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 test (
  id     serial PRIMARY KEY
, msrmnt integer
, entity integer
, localt timestamp
, val    float8
);

-- indexes irrelevant for this query;

-- reduced data set for demo:
INSERT INTO test (msrmnt, entity, localt, val)
SELECT msrmnt, entity, localt, random() AS val 
FROM   generate_series(timestamp '2012-01-01'
                     , timestamp '2012-01-01 00:10'
                     , interval  '1 min') AS localt
CROSS  JOIN generate_series(1,  5, 1) msrmnt
CROSS  JOIN generate_series(1, 20, 1) entity;

CREATE TABLE



INSERT 0 1100


SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM   crosstab(
  'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
        , localt, entity -- additional columns
        , msrmnt, val
   FROM   test
   ORDER  BY localt, entity, msrmnt'
, 'SELECT generate_series(1,5)$$   -- more?
  ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 15;

ERROR:  unterminated quoted string at or near "'SELECT generate_series(1,5)$$   -- more?
  ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 15;"
LINE 9: , 'SELECT generate_series(1,5)$$   -- more?
          ^


explain analyze
SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM   crosstab(
  'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
        , localt, entity -- additional columns
        , msrmnt, val
   FROM   test
   ORDER  BY localt, entity, msrmnt'
, 'SELECT generate_series(1,5)'  -- more?
   ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 15;


QUERY PLAN


Limit  (cost=0.00..0.15 rows=15 width=52) (actual time=13.532..13.538 rows=15 loops=1)

  ->  Function Scan on crosstab ct  (cost=0.00..10.00 rows=1000 width=52) (actual time=13.382..13.385 rows=15 loops=1)

Planning Time: 0.057 ms

Execution Time: 43.167 ms



EXPLAIN