Postgres 13

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

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