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:00'
                     , interval '1 min') AS localt
CROSS  JOIN generate_series(1,  5, 1) msrmnt
CROSS  JOIN generate_series(1, 20, 1) entity;
1100 rows affected

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


localt
entity
msrmnt01
msrmnt02
msrmnt03
msrmnt04
msrmnt05


2012-01-01 00:00:00
1
0.188145370222628
0.793924873229116
0.888584668748081
0.262206876650453
0.915562812704593

2012-01-01 00:00:00
2
0.980983615852892
0.118662754073739
0.205516632180661
0.489464178215712
0.79509011702612

2012-01-01 00:00:00
3
0.101330113131553
0.128867420367897
0.410733678378165
0.953999063000083
0.81025482993573

2012-01-01 00:00:00
4
0.778547182213515
0.935175867285579
0.544315747916698
0.0980605762451887
0.062856977339834

2012-01-01 00:00:00
5
0.701799887232482
0.852735056076199
0.927445561625063
0.703712262678891
0.693596348166466

2012-01-01 00:00:00
6
0.786178780719638
0.985564046073705
0.295394327025861
0.86158332368359
0.543377078603953

2012-01-01 00:00:00
7
0.408737627789378
0.674214544240385
0.276125773321837
0.635714163072407
0.0628861980512738

2012-01-01 00:00:00
8
0.415532590355724
0.376516985706985
0.303588771726936
0.70413912832737
0.577666624914855

2012-01-01 00:00:00
9
0.597592941951007
0.940891707781702
0.00402150768786669
0.177905212156475
0.950713871512562

2012-01-01 00:00:00
10
0.572262780275196
0.854870083741844
0.0286314100958407
0.588900340721011
0.43943240493536

2012-01-01 00:00:00
11
0.252330454997718
0.112917971331626
0.769638810306787
0.302158935461193
0.402628609910607

2012-01-01 00:00:00
12
0.812713871244341
0.12903707800433
0.797946380916983
0.0664898809045553
0.212920748163015

2012-01-01 00:00:00
13
0.655731078702956
0.835853699594736
0.147294164635241
0.794416972901672
0.928896583151072

2012-01-01 00:00:00
14
0.721928929444402
0.214248084463179
0.898506230674684
0.712892614305019
0.356627673376352

2012-01-01 00:00:00
15
0.884660648182034
0.907584260217845
0.733122248202562
0.610805628821254
0.310981324408203


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=3.334..3.338 rows=15 loops=1)

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

Planning Time: 0.022 ms

Execution Time: 3.373 ms

localt	entity	msrmnt01	msrmnt02	msrmnt03	msrmnt04	msrmnt05
2012-01-01 00:00:00	1	0.188145370222628	0.793924873229116	0.888584668748081	0.262206876650453	0.915562812704593
2012-01-01 00:00:00	2	0.980983615852892	0.118662754073739	0.205516632180661	0.489464178215712	0.79509011702612
2012-01-01 00:00:00	3	0.101330113131553	0.128867420367897	0.410733678378165	0.953999063000083	0.81025482993573
2012-01-01 00:00:00	4	0.778547182213515	0.935175867285579	0.544315747916698	0.0980605762451887	0.062856977339834
2012-01-01 00:00:00	5	0.701799887232482	0.852735056076199	0.927445561625063	0.703712262678891	0.693596348166466
2012-01-01 00:00:00	6	0.786178780719638	0.985564046073705	0.295394327025861	0.86158332368359	0.543377078603953
2012-01-01 00:00:00	7	0.408737627789378	0.674214544240385	0.276125773321837	0.635714163072407	0.0628861980512738
2012-01-01 00:00:00	8	0.415532590355724	0.376516985706985	0.303588771726936	0.70413912832737	0.577666624914855
2012-01-01 00:00:00	9	0.597592941951007	0.940891707781702	0.00402150768786669	0.177905212156475	0.950713871512562
2012-01-01 00:00:00	10	0.572262780275196	0.854870083741844	0.0286314100958407	0.588900340721011	0.43943240493536
2012-01-01 00:00:00	11	0.252330454997718	0.112917971331626	0.769638810306787	0.302158935461193	0.402628609910607
2012-01-01 00:00:00	12	0.812713871244341	0.12903707800433	0.797946380916983	0.0664898809045553	0.212920748163015
2012-01-01 00:00:00	13	0.655731078702956	0.835853699594736	0.147294164635241	0.794416972901672	0.928896583151072
2012-01-01 00:00:00	14	0.721928929444402	0.214248084463179	0.898506230674684	0.712892614305019	0.356627673376352
2012-01-01 00:00:00	15	0.884660648182034	0.907584260217845	0.733122248202562	0.610805628821254	0.310981324408203