Postgres 13

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 tasks (
   id   serial PRIMARY KEY
 , f_id integer
 , name text NOT NULL
 , sort integer
);

INSERT INTO tasks (id, f_id, name, sort) VALUES
  (1, 1, 'zeta', -1)
, (2, 1, 'alpha', 1)
, (3, 1, 'gamma', 3)
, (4, 1, 'beta', 2)
, (5, 1, 'delta', NULL)
, (6, 1, 'epsilon', NULL)
;

CREATE TABLE



INSERT 0 6


SELECT *
FROM   tasks
ORDER  BY (sort IS NOT DISTINCT FROM -1), sort;


id
f_id
name
sort


2
1
alpha
1

4
1
beta
2

3
1
gamma
3

5
1
delta
null

6
1
epsilon
null

1
1
zeta
-1



SELECT 6


-- equivalent
SELECT *
FROM   tasks
ORDER  BY (sort IS DISTINCT FROM -1) DESC, sort;


id
f_id
name
sort


2
1
alpha
1

4
1
beta
2

3
1
gamma
3

5
1
delta
null

6
1
epsilon
null

1
1
zeta
-1



SELECT 6