Postgres 14

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 person (
  person_id int
, full_name text
, phone text
, last_name text
);

INSERT INTO person VALUES 
  (1, 'full1', '123', 'name1')
, (2, 'full2', '123', 'name2')
, (4, 'full4', '123', 'name9')   -- last 3 rows share the same last_name
, (3, 'full3', '123', 'name9')
, (5, 'full5', '123', 'name9')
;

CREATE TABLE homes(
  person_id int
, address text
, appraisal text
);

INSERT INTO homes VALUES 
  (1, 'str1', 'app1')
, (2, 'str2', 'app2')
, (3, 'str3', 'app3')  -- appraisal would sort according to person_id ...
, (4, 'str4', 'app4')
, (5, 'str5', 'app5')
;

CREATE VIEW people AS
SELECT p.person_id
     , p.full_name
     , p.phone
FROM   person p
ORDER  BY p.last_name;
5 rows affected

5 rows affected

-- Show output from view
SELECT * from people;


person_id
full_name
phone


1
full1
123

2
full2
123

4
full4
123

3
full3
123

5
full5
123


-- Results with row_number() 
SELECT p.*
     , h.address
     , h.appraisal
FROM  (SELECT *, row_number() OVER () AS rn FROM people) p
LEFT   JOIN homes h ON h.person_id = p.person_id
ORDER  BY p.rn, h.appraisal;


person_id
full_name
phone
rn
address
appraisal


1
full1
123
1
str1
app1

2
full2
123
2
str2
app2

4
full4
123
3
str4
app4

3
full3
123
4
str3
app3

5
full5
123
5
str5
app5


-- PROPER results with rank()
SELECT p.*
     , h.address
     , h.appraisal
FROM  (SELECT *, rank() OVER () AS rnk FROM people) p
LEFT   JOIN homes h ON h.person_id = p.person_id
ORDER  BY p.rnk, h.appraisal;


person_id
full_name
phone
rnk
address
appraisal


1
full1
123
1
str1
app1

2
full2
123
1
str2
app2

3
full3
123
1
str3
app3

4
full4
123
1
str4
app4

5
full5
123
1
str5
app5

person_id	full_name	phone	rn	address	appraisal
1	full1	123	1	str1	app1
2	full2	123	2	str2	app2
4	full4	123	3	str4	app4
3	full3	123	4	str3	app3
5	full5	123	5	str5	app5