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

create table table_a (bic int, location text, type text, party text);

insert into table_a values 
(1, 'one', 'c', 'd'),
(2, 'one', 'x', 'e');

create table table_b (bic int, status text, cust_number text, live boolean, some_value int);
insert into table_b values 
(1, 'ok', '1', true, 10), (1, 'ok', '1', true, 100),
(2, 'ok', '1', true, 20), (2, 'ok', '1', true, 200);





CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 4


SELECT DISTINCT * 
FROM TABLE_A A 
  JOIN TABLE_B B ON A.BIC = B.BIC 
WHERE B.CUST_NUMBER= '1'
  AND A.LOCATION= 'one'
  AND B.STATUS= 'ok'
  AND B.LIVE = true
ORDER BY A.TYPE, A.PARTY;


ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 8: ORDER BY A.TYPE, A.PARTY;
                 ^


select *
from (
SELECT DISTINCT * 
FROM TABLE_A A 
  JOIN TABLE_B B ON A.BIC = B.BIC 
WHERE B.CUST_NUMBER= '1'
  AND A.LOCATION= 'one'
  AND B.STATUS= 'ok'
  AND B.LIVE = true

) a
ORDER BY A.TYPE, A.PARTY;


bic
location
type
party
bic
status
cust_number
live
some_value


1
one
c
d
1
ok
1
t
10

1
one
c
d
1
ok
1
t
100

2
one
x
e
2
ok
1
t
20

2
one
x
e
2
ok
1
t
200



SELECT 4