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?.
version |
---|
PostgreSQL 12.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
CREATE TABLE
INSERT 0 9
s | t1 | lag_t1_2 | lag_t1 | lag_t2 | l1 | l2 | lead_t1 | lead_t2 | lead_t1_2 |
---|---|---|---|---|---|---|---|---|---|
1 | 2019-01-01 00:00:00 | null | null | 4.60 | 5.07 | 5.07 | 4.60 | null | null |
2 | 2019-01-01 01:00:00 | null | 5.07 | null | 4.60 | 4.60 | null | 5.07 | null |
3 | 2019-01-01 02:00:00 | 5.07 | 4.60 | null | null | null | null | 4.60 | 4.7 |
4 | 2019-01-01 03:00:00 | 4.60 | null | 4.7 | null | null | 4.7 | null | 4.20 |
5 | 2019-01-01 04:00:00 | null | null | 4.20 | 4.7 | 4.7 | 4.20 | null | null |
6 | 2019-01-01 05:00:00 | null | 4.7 | null | 4.20 | 4.20 | null | 4.7 | 4.98 |
7 | 2019-01-01 06:00:00 | 4.7 | 4.20 | 4.98 | null | null | 4.98 | 4.20 | 4.50 |
8 | 2019-01-01 07:00:00 | 4.20 | null | 4.50 | 4.98 | 4.98 | 4.50 | null | null |
9 | 2019-01-01 08:00:00 | null | 4.98 | null | 4.50 | 4.50 | null | 4.98 | null |
SELECT 9
s | lat | final_val |
---|---|---|
1 | 5.07 | 5.07 |
2 | 4.60 | 4.60 |
3 | null | 4.84 |
4 | null | 4.45 |
5 | 4.7 | 4.7 |
6 | 4.20 | 4.20 |
7 | null | 4.59 |
8 | 4.98 | 4.98 |
9 | 4.50 | 4.50 |
SELECT 9