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
INSERT 0 32
site | dateval | percentile_cont |
---|---|---|
A | 2019-01-05 | {17,23,52,52,78} |
A | 2019-01-06 | {33,52,65,89,101} |
B | 2019-01-05 | {5,12,16,25,44} |
B | 2019-01-06 | {6,14,22,34,51} |
C | 2019-01-06 | {1,1.25,1.5,1.75,2} |
D | 2019-01-06 | {1,1.25,2,2,2} |
SELECT 6
site | dateval | raw | sumcnt | cumsumcnt | percentile |
---|---|---|---|---|---|
A | 2019-01-05 | 17 | 54 | 54 | 0.09872029250457038391 |
A | 2019-01-05 | 23 | 98 | 152 | 0.27787934186471663620 |
A | 2019-01-05 | 37 | 75 | 227 | 0.41499085923217550274 |
A | 2019-01-05 | 45 | 23 | 250 | 0.45703839122486288848 |
A | 2019-01-05 | 52 | 178 | 428 | 0.78244972577696526508 |
A | 2019-01-05 | 56 | 17 | 445 | 0.81352833638025594150 |
A | 2019-01-05 | 78 | 102 | 547 | 1.00000000000000000000 |
A | 2019-01-06 | 33 | 35 | 35 | 0.09668508287292817680 |
A | 2019-01-06 | 52 | 64 | 99 | 0.27348066298342541436 |
A | 2019-01-06 | 65 | 93 | 192 | 0.53038674033149171271 |
A | 2019-01-06 | 67 | 45 | 237 | 0.65469613259668508287 |
A | 2019-01-06 | 89 | 113 | 350 | 0.96685082872928176796 |
A | 2019-01-06 | 101 | 12 | 362 | 1.00000000000000000000 |
B | 2019-01-05 | 5 | 25 | 25 | 0.07042253521126760563 |
B | 2019-01-05 | 8 | 12 | 37 | 0.10422535211267605634 |
B | 2019-01-05 | 12 | 107 | 144 | 0.40563380281690140845 |
B | 2019-01-05 | 16 | 48 | 192 | 0.54084507042253521127 |
B | 2019-01-05 | 25 | 78 | 270 | 0.76056338028169014085 |
B | 2019-01-05 | 31 | 32 | 302 | 0.85070422535211267606 |
B | 2019-01-05 | 44 | 53 | 355 | 1.00000000000000000000 |
B | 2019-01-06 | 6 | 34 | 34 | 0.13709677419354838710 |
B | 2019-01-06 | 14 | 52 | 86 | 0.34677419354838709677 |
B | 2019-01-06 | 18 | 35 | 121 | 0.48790322580645161290 |
B | 2019-01-06 | 22 | 23 | 144 | 0.58064516129032258065 |
B | 2019-01-06 | 34 | 87 | 231 | 0.93145161290322580645 |
B | 2019-01-06 | 51 | 17 | 248 | 1.00000000000000000000 |
C | 2019-01-06 | 1 | 1 | 1 | 0.50000000000000000000 |
C | 2019-01-06 | 2 | 1 | 2 | 1.00000000000000000000 |
D | 2019-01-06 | 1 | 2 | 2 | 0.33333333333333333333 |
D | 2019-01-06 | 2 | 4 | 6 | 1.00000000000000000000 |
SELECT 30
site | dateval | raw | sumcnt | cumsumcnt | percentile |
---|---|---|---|---|---|
A | 2019-01-05 | 17 | 54 | 54 | 0.09872029250457038391 |
A | 2019-01-05 | 23 | 98 | 152 | 0.27787934186471663620 |
A | 2019-01-05 | 37 | 75 | 227 | 0.41499085923217550274 |
A | 2019-01-05 | 45 | 23 | 250 | 0.45703839122486288848 |
A | 2019-01-05 | 52 | 178 | 428 | 0.78244972577696526508 |
A | 2019-01-05 | 56 | 17 | 445 | 0.81352833638025594150 |
A | 2019-01-05 | 78 | 102 | 547 | 1.00000000000000000000 |
A | 2019-01-06 | 33 | 35 | 35 | 0.09668508287292817680 |
A | 2019-01-06 | 52 | 64 | 99 | 0.27348066298342541436 |
A | 2019-01-06 | 65 | 93 | 192 | 0.53038674033149171271 |
A | 2019-01-06 | 67 | 45 | 237 | 0.65469613259668508287 |
A | 2019-01-06 | 89 | 113 | 350 | 0.96685082872928176796 |
A | 2019-01-06 | 101 | 12 | 362 | 1.00000000000000000000 |
B | 2019-01-05 | 5 | 25 | 25 | 0.07042253521126760563 |
B | 2019-01-05 | 8 | 12 | 37 | 0.10422535211267605634 |
B | 2019-01-05 | 12 | 107 | 144 | 0.40563380281690140845 |
B | 2019-01-05 | 16 | 48 | 192 | 0.54084507042253521127 |
B | 2019-01-05 | 25 | 78 | 270 | 0.76056338028169014085 |
B | 2019-01-05 | 31 | 32 | 302 | 0.85070422535211267606 |
B | 2019-01-05 | 44 | 53 | 355 | 1.00000000000000000000 |
B | 2019-01-06 | 6 | 34 | 34 | 0.13709677419354838710 |
B | 2019-01-06 | 14 | 52 | 86 | 0.34677419354838709677 |
B | 2019-01-06 | 18 | 35 | 121 | 0.48790322580645161290 |
B | 2019-01-06 | 22 | 23 | 144 | 0.58064516129032258065 |
B | 2019-01-06 | 34 | 87 | 231 | 0.93145161290322580645 |
B | 2019-01-06 | 51 | 17 | 248 | 1.00000000000000000000 |
C | 2019-01-06 | 1 | 1 | 1 | 0.50000000000000000000 |
C | 2019-01-06 | 2 | 1 | 2 | 1.00000000000000000000 |
D | 2019-01-06 | 1 | 2 | 2 | 0.33333333333333333333 |
D | 2019-01-06 | 2 | 4 | 6 | 1.00000000000000000000 |
SELECT 30