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 t (id int, bar int);

INSERT INTO t VALUES
(1, 7),
(2, 8),
(3, 30),
(4, 25),
(5, 24),
(6, 24),
(7, 35),
(8, 40);
8 rows affected

SELECT
    id,
    bar,
    LAG(bar) OVER (ORDER BY id)
FROM
    t;


id
bar
lag


1
7
null

2
8
7

3
30
8

4
25
30

5
24
25

6
24
24

7
35
24

8
40
35


SELECT
    id,
    bar,
    CASE 
        WHEN LAG(bar) OVER (ORDER BY id) < bar THEN 'Lower'
        WHEN LAG(bar) OVER (ORDER BY id) > bar THEN 'Higher'
        WHEN LAG(bar) OVER (ORDER BY id) = bar THEN 'Equal'
        ELSE 'Nothing to compare'
    END As Previous
FROM
    t;


id
bar
previous


1
7
Nothing to compare

2
8
Lower

3
30
Lower

4
25
Higher

5
24
Higher

6
24
Equal

7
35
Lower

8
40
Lower

id	bar	previous
1	7	Nothing to compare
2	8	Lower
3	30	Lower
4	25	Higher
5	24	Higher
6	24	Equal
7	35	Lower
8	40	Lower