Postgres 12

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?.

select version();


version


PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE contact_info
(
  id INT NOT NULL, 
  pri_contact_id INT,
  sec_contact_id INT,
  blah_blah VARCHAR(32)
);
INSERT INTO contact_info
VALUES (1, 1, NULL, 'Team A'), (2, 1, 3, 'Team A'), (3, 4, 2, 'Team B'), 
(4, 1, NULL, 'Team A'), (5, 1, 2, 'Team A');
5 rows affected

SELECT * FROM contact_info ORDER BY blah_blah, (- pri_contact_id) DESC;


id
pri_contact_id
sec_contact_id
blah_blah


1
1
null
Team A

2
1
3
Team A

4
1
null
Team A

5
1
2
Team A

3
4
2
Team B


SELECT * FROM contact_info ORDER BY blah_blah, (0 - pri_contact_id) DESC;


id
pri_contact_id
sec_contact_id
blah_blah


1
1
null
Team A

2
1
3
Team A

4
1
null
Team A

5
1
2
Team A

3
4
2
Team B


-- 
-- CORRECT RESULT - in line with SQL Standard
--
SELECT * FROM contact_info ORDER BY blah_blah, sec_contact_id NULLS LAST;


id
pri_contact_id
sec_contact_id
blah_blah


5
1
2
Team A

2
1
3
Team A

1
1
null
Team A

4
1
null
Team A

3
4
2
Team B


-- 
-- CORRECT RESULT - works on MySQL as well!
--


SELECT * FROM contact_info order by blah_blah, 
CASE WHEN (sec_contact_id IS NULL) THEN 0 ELSE 1 END DESC, sec_contact_id ASC;


id
pri_contact_id
sec_contact_id
blah_blah


5
1
2
Team A

2
1
3
Team A

1
1
null
Team A

4
1
null
Team A

3
4
2
Team B




SELECT * FROM contact_info ORDER BY blah_blah,  sec_contact_id IS NULL DESC



id
pri_contact_id
sec_contact_id
blah_blah


1
1
null
Team A

4
1
null
Team A

2
1
3
Team A

5
1
2
Team A

3
4
2
Team B