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 17.2 (Debian 17.2-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
SELECT 1
CREATE TABLE subscriptions (
customer_id INT,
plan_id INT,
start_date DATE
);
CREATE TABLE plans (
plan_id INT,
plan_name VARCHAR(50),
price INT
);
CREATE TABLE
CREATE TABLE
INSERT INTO subscriptions (customer_id, plan_id, start_date) VALUES
(1, 1, '2020-01-01'),
(2, 1, '2020-02-01'),
(3, 2, '2020-03-01');
INSERT INTO plans (plan_id, plan_name, price) VALUES
(1, 'plan1', 10),
(2, 'plan2', 100);
INSERT 0 3
INSERT 0 2
WITH RECURSIVE
subscription_info AS (
SELECT
s.customer_id,
s.plan_id,
p.plan_name,
s.start_date,
LEAD(s.start_date) OVER(
PARTITION BY s.customer_id
ORDER BY s.customer_id
) AS next_date,
p.price
FROM
subscriptions AS s
LEFT JOIN
plans AS p
ON s.plan_id = p.plan_id
WHERE
p.plan_id != 0 AND
EXTRACT(YEAR FROM start_date) = '2020' ),
basic_monthly_payments AS (
SELECT
customer_id,
plan_id,
plan_name,
-- start_date AS payment_date,
start_date::timestamp AS payment_date,
price AS amount,
next_date
FROM
subscription_info
WHERE
plan_id = 1
UNION ALL
| customer_id | plan_id | plan_name | payment_date | amount | next_date |
|---|---|---|---|---|---|
| 1 | 1 | plan1 | 2020-01-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-02-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-02-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-03-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-03-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-04-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-04-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-05-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-05-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-06-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-06-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-07-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-07-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-08-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-08-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-09-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-09-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-10-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-10-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-11-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-11-01 00:00:00 | 10 | null |
| 2 | 1 | plan1 | 2020-12-01 00:00:00 | 10 | null |
| 1 | 1 | plan1 | 2020-12-01 00:00:00 | 10 | null |
SELECT 23