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 campaigns (
id SERIAL PRIMARY KEY,
campaign VARCHAR,
supplier VARCHAR,
plan_quantity DECIMAL
);
INSERT INTO campaigns
(campaign, supplier, plan_quantity)
VALUES
('C001', 'supplier_a', '500'),
('C001', 'supplier_a', '500'),
('C001', 'supplier_b', '500'),
('C002', 'supplier_a', '600'),
('C002', 'supplier_b', '700'),
('C003', 'supplier_c', '100'),
('C003', 'supplier_c', '100'),
('C004', 'supplier_a', '900'),
('C004', 'supplier_c', '800'),
('C004', 'supplier_d', '250'),
('C004', 'supplier_d', '250'),
('C005', 'supplier_b', '380'),
('C005', 'supplier_b', '270'),
('C005', 'supplier_d', '590');
14 rows affected
SELECT
campaign AS campaign,
supplier AS supplier,
plan_quantity AS plan_quantity,
(CASE WHEN
MIN(plan_quantity) OVER(PARTITION BY supplier, campaign) = MAX(plan_quantity) OVER(PARTITION BY supplier, campaign)
THEN 'same' ELSE 'different' END) AS check
FROM campaigns
ORDER BY 1,2,3;
| campaign | supplier | plan_quantity | check |
|---|---|---|---|
| C001 | supplier_a | 500 | same |
| C001 | supplier_a | 500 | same |
| C001 | supplier_b | 500 | same |
| C002 | supplier_a | 600 | same |
| C002 | supplier_b | 700 | same |
| C003 | supplier_c | 100 | same |
| C003 | supplier_c | 100 | same |
| C004 | supplier_a | 900 | same |
| C004 | supplier_c | 800 | same |
| C004 | supplier_d | 250 | same |
| C004 | supplier_d | 250 | same |
| C005 | supplier_b | 270 | different |
| C005 | supplier_b | 380 | different |
| C005 | supplier_d | 590 | same |
SELECT
campaign AS campaign,
supplier AS supplier,
plan_quantity AS plan_quantity,
(CASE WHEN
Min(plan_quantity) OVER(PARTITION BY campaign) = MAX(plan_quantity) OVER(PARTITION BY campaign)
THEN 'same' ELSE 'different' END) AS check
FROM campaigns
ORDER BY 1,2,3;
| campaign | supplier | plan_quantity | check |
|---|---|---|---|
| C001 | supplier_a | 500 | same |
| C001 | supplier_a | 500 | same |
| C001 | supplier_b | 500 | same |
| C002 | supplier_a | 600 | different |
| C002 | supplier_b | 700 | different |
| C003 | supplier_c | 100 | same |
| C003 | supplier_c | 100 | same |
| C004 | supplier_a | 900 | different |
| C004 | supplier_c | 800 | different |
| C004 | supplier_d | 250 | different |
| C004 | supplier_d | 250 | different |
| C005 | supplier_b | 270 | different |
| C005 | supplier_b | 380 | different |
| C005 | supplier_d | 590 | different |