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 SERIAL PRIMARY KEY,
order_date DATE,
customer VARCHAR(255)
);
INSERT INTO customers
(order_date, customer)
VALUES
('2020-04-10', 'user_01'),
('2020-05-15', 'user_01'),
('2020-09-08', 'user_02'),
('2020-11-23', 'user_03'),
('2020-01-03', 'user_04'),
('2020-06-03', 'user_04'),
('2020-06-03', 'user_04'),
('2020-07-01', 'user_05'),
('2020-09-24', 'user_06'),
('2021-05-02', 'user_01'),
('2021-05-05', 'user_02'),
('2021-05-12', 'user_03'),
('2021-05-19', 'user_03'),
('2021-05-20', 'user_07'),
('2021-06-08', 'user_04'),
('2021-06-20', 'user_05'),
('2021-06-21', 'user_05'),
('2021-06-25', 'user_08');
18 rows affected
SELECT
c1.order_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2021-05-01 00:00:00' AND '2021-05-31 23:59:59'
AND EXISTS
(SELECT
c2.customer
FROM customers c2
WHERE c2.order_date BETWEEN '2020-06-01 00:00:00' AND '2021-04-30 23:59:59'
AND c2.customer = c1.customer)
ORDER BY 2;
| order_date | customer |
|---|---|
| 2021-05-05 | user_02 |
| 2021-05-12 | user_03 |
| 2021-05-19 | user_03 |