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 example (
  id serial PRIMARY KEY
, val VARCHAR (255) NOT NULL
, x integer NOT NULL
, y integer NOT NULL
);
    
insert into example (val,x,y) VALUES
  ('First Value (This should be first order)',7,2)
, ('Second Value (This should be second order)',6,3)
, ('Third Value (This should be third order)',5,4)
, ('Seventh Value (And This should be last order)',4,1)
, ('Sixth Value (This should be sixth order)',3,5)
, ('Fifth Value (This should be fifth order)',2,6)
, ('Fourth Value (This should be fourth order)',1,7)
;
7 rows affected

WITH cte AS (TABLE example ORDER BY x DESC LIMIT 3)
TABLE cte
UNION ALL
(
SELECT e.*
FROM   example e
LEFT   JOIN cte USING (id)
WHERE  cte.id IS NULL
ORDER  BY e.y DESC
);


id
val
x
y


1
First Value (This should be first order)
7
2

2
Second Value (This should be second order)
6
3

3
Third Value (This should be third order)
5
4

7
Fourth Value (This should be fourth order)
1
7

6
Fifth Value (This should be fifth order)
2
6

5
Sixth Value (This should be sixth order)
3
5

4
Seventh Value (And This should be last order)
4
1


-- WITHOUT UNION:
SELECT e.*
FROM   example  e
LEFT   JOIN (
   SELECT id, x FROM example ORDER BY x DESC LIMIT 3
   ) c USING (id)
ORDER  BY c.x DESC NULLS LAST, e.y DESC;


id
val
x
y


1
First Value (This should be first order)
7
2

2
Second Value (This should be second order)
6
3

3
Third Value (This should be third order)
5
4

7
Fourth Value (This should be fourth order)
1
7

6
Fifth Value (This should be fifth order)
2
6

5
Sixth Value (This should be sixth order)
3
5

4
Seventh Value (And This should be last order)
4
1

id	val	x	y
1	First Value (This should be first order)	7	2
2	Second Value (This should be second order)	6	3
3	Third Value (This should be third order)	5	4
7	Fourth Value (This should be fourth order)	1	7
6	Fifth Value (This should be fifth order)	2	6
5	Sixth Value (This should be sixth order)	3	5
4	Seventh Value (And This should be last order)	4	1