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;

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;


localt
entity
msrmnt01
msrmnt02
msrmnt03
msrmnt04
msrmnt05


2012-01-01 00:00:00
1
0.221211541909724
0.110111966729164
0.914453349076211
0.700245745014399
0.534119925461709

2012-01-01 00:00:00
2
0.433134899009019
0.65937156509608
0.129120713099837
0.0426272344775498
0.516901080496609

2012-01-01 00:00:00
3
0.353533728513867
0.341382754966617
0.636220161803067
0.087858434766531
0.966755141504109

2012-01-01 00:00:00
4
0.46412055240944
0.707513152156025
0.731627638451755
0.973691343795508
0.859531732741743

2012-01-01 00:00:00
5
0.384817112237215
0.293900552671403
0.233507996890694
0.180677832569927
0.0751115577295423

2012-01-01 00:00:00
6
0.778020455501974
0.375145475380123
0.877623376436532
0.239835998974741
0.690827887505293

2012-01-01 00:00:00
7
0.0879388130269945
0.135862299241126
0.742246319074184
0.631954054813832
0.478844093158841

2012-01-01 00:00:00
8
0.689496343955398
0.638798004947603
0.957731537986547
0.0967488856986165
0.00846751080825925

2012-01-01 00:00:00
9
0.51708444301039
0.466645469889045
0.906447626650333
0.479752376675606
0.550762932747602

2012-01-01 00:00:00
10
0.674536971375346
0.840310699306428
0.666040241718292
0.979713109787554
0.736954458523542

2012-01-01 00:00:00
11
0.781361658126116
0.784516869112849
0.216827722266316
0.529904382303357
0.40846893331036

2012-01-01 00:00:00
12
0.203064665198326
0.687857011798769
0.0165595938451588
0.394205725751817
0.251008677762002

2012-01-01 00:00:00
13
0.817174289375544
0.273445598781109
0.325411807280034
0.108833822887391
0.779581693001091

2012-01-01 00:00:00
14
0.104387283790857
0.138050597626716
0.558210477232933
0.166124544106424
0.496327368076891

2012-01-01 00:00:00
15
0.241403214633465
0.15197756420821
0.724072746001184
0.125833364203572
0.22470002155751



SELECT 15


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=11.614..11.620 rows=15 loops=1)

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

Planning Time: 0.122 ms

Execution Time: 11.687 ms



EXPLAIN

localt	entity	msrmnt01	msrmnt02	msrmnt03	msrmnt04	msrmnt05
2012-01-01 00:00:00	1	0.221211541909724	0.110111966729164	0.914453349076211	0.700245745014399	0.534119925461709
2012-01-01 00:00:00	2	0.433134899009019	0.65937156509608	0.129120713099837	0.0426272344775498	0.516901080496609
2012-01-01 00:00:00	3	0.353533728513867	0.341382754966617	0.636220161803067	0.087858434766531	0.966755141504109
2012-01-01 00:00:00	4	0.46412055240944	0.707513152156025	0.731627638451755	0.973691343795508	0.859531732741743
2012-01-01 00:00:00	5	0.384817112237215	0.293900552671403	0.233507996890694	0.180677832569927	0.0751115577295423
2012-01-01 00:00:00	6	0.778020455501974	0.375145475380123	0.877623376436532	0.239835998974741	0.690827887505293
2012-01-01 00:00:00	7	0.0879388130269945	0.135862299241126	0.742246319074184	0.631954054813832	0.478844093158841
2012-01-01 00:00:00	8	0.689496343955398	0.638798004947603	0.957731537986547	0.0967488856986165	0.00846751080825925
2012-01-01 00:00:00	9	0.51708444301039	0.466645469889045	0.906447626650333	0.479752376675606	0.550762932747602
2012-01-01 00:00:00	10	0.674536971375346	0.840310699306428	0.666040241718292	0.979713109787554	0.736954458523542
2012-01-01 00:00:00	11	0.781361658126116	0.784516869112849	0.216827722266316	0.529904382303357	0.40846893331036
2012-01-01 00:00:00	12	0.203064665198326	0.687857011798769	0.0165595938451588	0.394205725751817	0.251008677762002
2012-01-01 00:00:00	13	0.817174289375544	0.273445598781109	0.325411807280034	0.108833822887391	0.779581693001091
2012-01-01 00:00:00	14	0.104387283790857	0.138050597626716	0.558210477232933	0.166124544106424	0.496327368076891
2012-01-01 00:00:00	15	0.241403214633465	0.15197756420821	0.724072746001184	0.125833364203572	0.22470002155751