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?.

select version();


version


PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit


CREATE TABLE rtbinds (
    cons_name text,
    pricedate timestamp,
    shadow double precision
);

INSERT INTO rtbinds (cons_name, pricedate, shadow)
SELECT
    concat('TMP', (random() * 8 + 1)::int * 100, '_', ((random() + 2) * 10)::int * 1000),
    ts::date,
    random() * 333
FROM generate_series('2018-11-01'::timestamp, '2019-06-09'::timestamp, '0.147 hours'::interval) AS ts;
35919 rows affected

WITH
    days (c_day, c_visible, c_lookback) as (
        SELECT gen::date, (CASE WHEN gen::date < '2019-05-26' THEN false ELSE true END), gen::date - 6
        FROM generate_series('2019-05-26'::date - 6, '2019-06-01'::date, '1 day'::interval) AS gen
    ),
    daily (cons_name, pricedate, shadow_sum) AS (
        SELECT
            r.cons_name,
            r.pricedate::date,
            coalesce(sum(r.shadow), 0)
        FROM days
        LEFT JOIN rtbinds AS r ON (r.pricedate::date = days.c_day)
        GROUP BY 1, 2
    ),
    calc (cons_name, pricedate, shadow_sum) AS (
        SELECT
            cons_name,
            pricedate,
            sum(shadow_sum) OVER (PARTITION BY cons_name ORDER BY pricedate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        FROM daily
    ),
    numbered (cons_name, pricedate, shadow_sum, position) AS (
        SELECT
            calc.cons_name,
            calc.pricedate,
            calc.shadow_sum,
            ROW_NUMBER() OVER (PARTITION BY calc.pricedate ORDER BY calc.shadow_sum DESC)
        FROM calc
    )
SELECT
    days.c_lookback,
    numbered.cons_name,
    numbered.shadow_sum
FROM numbered
INNER JOIN days ON (days.c_day = numbered.pricedate AND days.c_visible)
WHERE numbered.position < 11
ORDER BY numbered.pricedate DESC, numbered.shadow_sum DESC
;


c_lookback
cons_name
shadow_sum


2019-05-26
TMP500_28000
4168.52775960881

2019-05-26
TMP400_24000
3964.11754470086

2019-05-26
TMP700_23000
3903.61425492307

2019-05-26
TMP800_26000
3719.82413867069

2019-05-26
TMP100_23000
3711.90923438687

2019-05-26
TMP500_26000
3556.75128493365

2019-05-26
TMP700_22000
3460.16010084283

2019-05-26
TMP700_27000
3426.4721040898

2019-05-26
TMP200_27000
3426.30662501184

2019-05-26
TMP500_29000
3326.53794716299

2019-05-25
TMP400_21000
4358.81973354472

2019-05-25
TMP500_28000
4187.92859432939

2019-05-25
TMP300_29000
3902.88013745891

2019-05-25
TMP700_22000
3711.19192366535

2019-05-25
TMP500_26000
3668.89470404573

2019-05-25
TMP300_22000
3657.49760659039

2019-05-25
TMP400_24000
3642.84038934577

2019-05-25
TMP700_27000
3604.43667848082

2019-05-25
TMP800_22000
3450.87862129603

2019-05-25
TMP700_23000
3448.79839996761

2019-05-24
TMP300_29000
4179.41420035064

2019-05-24
TMP500_29000
3905.89899966354

2019-05-24
TMP500_26000
3883.3612621394

2019-05-24
TMP800_26000
3848.67539316695

2019-05-24
TMP400_21000
3748.1731114299

2019-05-24
TMP800_27000
3744.95815546811

2019-05-24
TMP700_27000
3680.50231570238

2019-05-24
TMP700_22000
3559.07110232301

2019-05-24
TMP700_23000
3443.85438598506

2019-05-24
TMP800_22000
3434.86766896304

2019-05-23
TMP500_26000
4146.8646031972

2019-05-23
TMP500_29000
4119.60893940087

2019-05-23
TMP800_27000
4043.9310645978

2019-05-23
TMP800_24000
3680.41013501817

2019-05-23
TMP800_26000
3674.99496826949

2019-05-23
TMP600_21000
3625.74402226461

2019-05-23
TMP800_21000
3581.54297776055

2019-05-23
TMP300_29000
3573.41832576273

2019-05-23
TMP800_23000
3568.63900607312

2019-05-23
TMP700_27000
3565.28792348178

2019-05-22
TMP700_27000
4143.55178992404

2019-05-22
TMP800_27000
3869.09507841384

2019-05-22
TMP300_22000
3734.47173660016

2019-05-22
TMP500_26000
3681.56329527497

2019-05-22
TMP500_29000
3668.76466233004

2019-05-22
TMP800_26000
3637.1132886163

2019-05-22
TMP800_22000
3601.31222524261

2019-05-22
TMP800_24000
3575.3576390259

2019-05-22
TMP600_25000
3559.69380186824

2019-05-22
TMP800_23000
3512.94234188274

2019-05-21
TMP100_23000
4065.95092014503

2019-05-21
TMP800_23000
4025.41303874971

2019-05-21
TMP700_27000
3886.44202063791

2019-05-21
TMP800_27000
3807.71025098814

2019-05-21
TMP800_26000
3763.77562370896

2019-05-21
TMP300_22000
3523.6160829342

2019-05-21
TMP800_24000
3396.15605131583

2019-05-21
TMP600_25000
3363.07149826549

2019-05-21
TMP500_22000
3350.37515532598

2019-05-21
TMP600_22000
3321.36462057894

2019-05-20
TMP600_25000
3877.64836011082

2019-05-20
TMP800_23000
3867.56880909065

2019-05-20
TMP700_27000
3840.35012229905

2019-05-20
TMP500_29000
3721.14473523851

2019-05-20
TMP800_24000
3588.99344313657

2019-05-20
TMP800_22000
3570.14274054486

2019-05-20
TMP300_27000
3466.10718762176

2019-05-20
TMP600_22000
3380.38800320495

2019-05-20
TMP500_22000
3330.87195787439

2019-05-20
TMP100_23000
3294.23912720243

c_lookback	cons_name	shadow_sum
2019-05-26	TMP500_28000	4168.52775960881
2019-05-26	TMP400_24000	3964.11754470086
2019-05-26	TMP700_23000	3903.61425492307
2019-05-26	TMP800_26000	3719.82413867069
2019-05-26	TMP100_23000	3711.90923438687
2019-05-26	TMP500_26000	3556.75128493365
2019-05-26	TMP700_22000	3460.16010084283
2019-05-26	TMP700_27000	3426.4721040898
2019-05-26	TMP200_27000	3426.30662501184
2019-05-26	TMP500_29000	3326.53794716299
2019-05-25	TMP400_21000	4358.81973354472
2019-05-25	TMP500_28000	4187.92859432939
2019-05-25	TMP300_29000	3902.88013745891
2019-05-25	TMP700_22000	3711.19192366535
2019-05-25	TMP500_26000	3668.89470404573
2019-05-25	TMP300_22000	3657.49760659039
2019-05-25	TMP400_24000	3642.84038934577
2019-05-25	TMP700_27000	3604.43667848082
2019-05-25	TMP800_22000	3450.87862129603
2019-05-25	TMP700_23000	3448.79839996761
2019-05-24	TMP300_29000	4179.41420035064
2019-05-24	TMP500_29000	3905.89899966354
2019-05-24	TMP500_26000	3883.3612621394
2019-05-24	TMP800_26000	3848.67539316695
2019-05-24	TMP400_21000	3748.1731114299
2019-05-24	TMP800_27000	3744.95815546811
2019-05-24	TMP700_27000	3680.50231570238
2019-05-24	TMP700_22000	3559.07110232301
2019-05-24	TMP700_23000	3443.85438598506
2019-05-24	TMP800_22000	3434.86766896304
2019-05-23	TMP500_26000	4146.8646031972
2019-05-23	TMP500_29000	4119.60893940087
2019-05-23	TMP800_27000	4043.9310645978
2019-05-23	TMP800_24000	3680.41013501817
2019-05-23	TMP800_26000	3674.99496826949
2019-05-23	TMP600_21000	3625.74402226461
2019-05-23	TMP800_21000	3581.54297776055
2019-05-23	TMP300_29000	3573.41832576273
2019-05-23	TMP800_23000	3568.63900607312
2019-05-23	TMP700_27000	3565.28792348178
2019-05-22	TMP700_27000	4143.55178992404
2019-05-22	TMP800_27000	3869.09507841384
2019-05-22	TMP300_22000	3734.47173660016
2019-05-22	TMP500_26000	3681.56329527497
2019-05-22	TMP500_29000	3668.76466233004
2019-05-22	TMP800_26000	3637.1132886163
2019-05-22	TMP800_22000	3601.31222524261
2019-05-22	TMP800_24000	3575.3576390259
2019-05-22	TMP600_25000	3559.69380186824
2019-05-22	TMP800_23000	3512.94234188274
2019-05-21	TMP100_23000	4065.95092014503
2019-05-21	TMP800_23000	4025.41303874971
2019-05-21	TMP700_27000	3886.44202063791
2019-05-21	TMP800_27000	3807.71025098814
2019-05-21	TMP800_26000	3763.77562370896
2019-05-21	TMP300_22000	3523.6160829342
2019-05-21	TMP800_24000	3396.15605131583
2019-05-21	TMP600_25000	3363.07149826549
2019-05-21	TMP500_22000	3350.37515532598
2019-05-21	TMP600_22000	3321.36462057894
2019-05-20	TMP600_25000	3877.64836011082
2019-05-20	TMP800_23000	3867.56880909065
2019-05-20	TMP700_27000	3840.35012229905
2019-05-20	TMP500_29000	3721.14473523851
2019-05-20	TMP800_24000	3588.99344313657
2019-05-20	TMP800_22000	3570.14274054486
2019-05-20	TMP300_27000	3466.10718762176
2019-05-20	TMP600_22000	3380.38800320495
2019-05-20	TMP500_22000	3330.87195787439
2019-05-20	TMP100_23000	3294.23912720243