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 t (
id serial PRIMARY KEY,
Type VARCHAR (50) NOT NULL,
InvoiceNo int NOT NULL,
ClientId VARCHAR (50) NOT NULL);
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '97', '1');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '2');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '2');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '98', '2');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '99', '1');
insert into t (type, InvoiceNo, ClientId) values ('T', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '100', '1');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select *
from t


id
type
invoiceno
clientid


1
X
0
1

2
I
97
1

3
S
0
2

4
X
0
2

5
S
0
1

6
I
98
2

7
S
0
1

8
X
0
1

9
I
99
1

10
T
0
1

11
S
0
1

12
X
0
1

13
I
100
1


select *
from (select t.*,
             min(InvoiceNo) filter (where type = 'I') over (partition by clientid order by id desc) as imputed_invoiceno 
      from t
     ) x
where Type <> 'I'


id
type
invoiceno
clientid
imputed_invoiceno


12
X
0
1
100

11
S
0
1
100

10
T
0
1
100

8
X
0
1
99

7
S
0
1
99

5
S
0
1
99

1
X
0
1
97

4
X
0
2
98

3
S
0
2
98


alter table t alter column InvoiceNo type varchar(255)
select *
from (select t.*,
             min(InvoiceNo) filter (where type = 'I') over (partition by clientid order by id desc) as imputed_invoiceno 
      from t
     ) x
where Type <> 'I'


id
type
invoiceno
clientid
imputed_invoiceno


12
X
0
1
100

11
S
0
1
100

10
T
0
1
100

8
X
0
1
100

7
S
0
1
100

5
S
0
1
100

1
X
0
1
100

4
X
0
2
98

3
S
0
2
98

id	type	invoiceno	clientid
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

id	type	clientid	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	97
4	X	2	98
3	S	2	98

id	type	clientid	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	100
7	S	1	100
5	S	1	100
1	X	1	100
4	X	2	98
3	S	2	98

id	type	invoiceno	clientid
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

id	type	clientid	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	97
4	X	2	98
3	S	2	98

id	type	clientid	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	100
7	S	1	100
5	S	1	100
1	X	1	100
4	X	2	98
3	S	2	98

id	type	invoiceno	clientid
1	X	0	1
2	I	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	I	98	2
7	S	0	1
8	X	0	1
9	I	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	I	100	1

id	type	clientid	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	99
7	S	1	99
5	S	1	99
1	X	1	97
4	X	2	98
3	S	2	98

id	type	clientid	imputed_invoiceno
12	X	1	100
11	S	1	100
10	T	1	100
8	X	1	100
7	S	1	100
5	S	1	100
1	X	1	100
4	X	2	98
3	S	2	98