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 customer_orders (customer_id int, timestamp timestamp);
INSERT INTO customer_orders VALUES 
  (6778, '2022-01-01')
, (6778, '2022-02-05')
, (5544, '2022-04-01')
, (6778, '2022-02-04')
, (5544, '2022-04-03')
, (5544, '2022-04-02')
, (5544, '2022-01-01')
, (6778, '2021-01-01')
;

-- ideally, this index:
CREATE UNIQUE INDEX ON customer_orders (customer_id, timestamp);
8 rows affected

SELECT customer_id, timestamp
FROM  (
   SELECT row_number() OVER (PARTITION BY customer_id ORDER BY timestamp) AS rn
        , customer_id, timestamp
   FROM   customer_orders
   ) sub
WHERE  rn <= 3 
ORDER  BY 1, 2;


customer_id
timestamp


5544
2022-01-01 00:00:00

5544
2022-04-01 00:00:00

5544
2022-04-02 00:00:00

6778
2021-01-01 00:00:00

6778
2022-01-01 00:00:00

6778
2022-02-04 00:00:00


SELECT customer_id, timestamp
FROM  (
   SELECT row_number() OVER (PARTITION BY customer_id ORDER BY timestamp
                             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rn  -- identical result, but ~ 20 % faster!
        , customer_id, timestamp
   FROM   customer_orders
   ) sub
WHERE  rn <= 3 
ORDER  BY 1, 2;


customer_id
timestamp


5544
2022-01-01 00:00:00

5544
2022-04-01 00:00:00

5544
2022-04-02 00:00:00

6778
2021-01-01 00:00:00

6778
2022-01-01 00:00:00

6778
2022-02-04 00:00:00


-- parent table exists!
CREATE TABLE customers (customer_id int PRIMARY KEY);  -- rest irrelevant
INSERT INTO customers VALUES 
  (5544)
, (6778)
;
-- PK index is all we need (and not important in this case)
2 rows affected

-- orders of magnitude faster for many rows per customer:
SELECT c.customer_id, o.timestamp
FROM   customers c
CROSS  JOIN LATERAL (
   SELECT timestamp
   FROM   customer_orders o
   WHERE  o.customer_id = c.customer_id
   ORDER  BY o.timestamp
   LIMIT  3
   ) o
ORDER  BY 1, 2;


customer_id
timestamp


5544
2022-01-01 00:00:00

5544
2022-04-01 00:00:00

5544
2022-04-02 00:00:00

6778
2021-01-01 00:00:00

6778
2022-01-01 00:00:00

6778
2022-02-04 00:00:00

customer_id	timestamp
5544	2022-01-01 00:00:00
5544	2022-04-01 00:00:00
5544	2022-04-02 00:00:00
6778	2021-01-01 00:00:00
6778	2022-01-01 00:00:00
6778	2022-02-04 00:00:00