Postgres 11

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 customers (
  id int NOT NULL,
  company varchar(255),
  first_name varchar(255) default NULL,
  last_name varchar(255) default NULL,
  PRIMARY KEY (id)
);

CREATE TABLE transactions (
  id int NOT NULL,
  form_type varchar(255) default NULL,
  customer_id int default NULL,
  due decimal default NULL,
  PRIMARY KEY (id)
);

INSERT INTO customers (id, company, first_name, last_name) VALUES
(1, 'MDD', 'T', 'H'),
(2, 'Aliance Magnet A LLP', 'A', 'Wilkinson'),
(3, 'MAF', 'C', 'G'),
(4, 'QL', 'F', 'B'),
(5, 'ARL', 'S', 'P'),
(6, 'Q Corp.', 'H', 'H'),
(7, 'VQDA', 'L', 'W'),
(8, 'AESC', 'E', 'W'),
(9, 'Placement Incorporated', 'C', 'Mendez'),
(10, 'Numpties United', 'Y', 'Cunningham');

INSERT INTO transactions (id, form_type, customer_id, due) VALUES
(1, 'invoice', 9, 1.08),
(2, 'payment', 1, 6.32),
(3, 'invoice', 7, 9.42),
(4, 'payment', 9, 3.58),
(5, 'invoice', 7, 5.35),
(6, 'payment', 3, 5.42),
(7, 'invoice', 9, 5.32),
(8, 'invoice', 9, 9.62),
(9, 'invoice', 10, 1.40),
(10, 'invoice', 2, 3.72);
10 rows affected

10 rows affected

SELECT
  t.id AS trans_id,
  c.id AS customer_id,
  c.company,
  c.first_name,
  c.last_name,
  t.due
FROM (
  SELECT DISTINCT c.*
  FROM customers AS c
  JOIN transactions AS t ON t.customer_id = c.id
  WHERE t.due > 0
  AND t.form_type = 'invoice'
  ORDER BY c.company, c.first_name, c.last_name
  LIMIT 3
) AS c
JOIN transactions AS t ON t.customer_id = c.id
WHERE t.due > 0 AND t.form_type = 'invoice'
ORDER BY c.company, c.first_name, c.last_name;


trans_id
customer_id
company
first_name
last_name
due


10
2
Aliance Magnet A LLP
A
Wilkinson
3.72

9
10
Numpties United
Y
Cunningham
1.40

1
9
Placement Incorporated
C
Mendez
1.08

7
9
Placement Incorporated
C
Mendez
5.32

8
9
Placement Incorporated
C
Mendez
9.62


WITH cte AS (
  SELECT t.id AS trans_id,
  c.id AS customer_id,
  c.company,
  c.first_name,
  c.last_name,
  t.due, DENSE_RANK() OVER(ORDER BY  c.company, c.first_name, c.last_name) rn
  FROM customers AS c
  JOIN transactions AS t ON t.customer_id = c.id
  WHERE t.due > 0  AND t.form_type = 'invoice'
)
SELECT * FROM cte WHERE rn <= 3;


trans_id
customer_id
company
first_name
last_name
due
rn


10
2
Aliance Magnet A LLP
A
Wilkinson
3.72
1

9
10
Numpties United
Y
Cunningham
1.40
2

8
9
Placement Incorporated
C
Mendez
9.62
3

7
9
Placement Incorporated
C
Mendez
5.32
3

1
9
Placement Incorporated
C
Mendez
1.08
3

trans_id	customer_id	company	first_name	last_name	due
10	2	Aliance Magnet A LLP	A	Wilkinson	3.72
9	10	Numpties United	Y	Cunningham	1.40
1	9	Placement Incorporated	C	Mendez	1.08
7	9	Placement Incorporated	C	Mendez	5.32
8	9	Placement Incorporated	C	Mendez	9.62