Postgres 12

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 product (
                id serial NOT NULL,
                opid int4 NULL,
                opvalue int4 NULL,
                info varchar NULL,
                CONSTRAINT product_pkey PRIMARY KEY (id)
            );
INSERT INTO product (id,opid,opvalue,info) VALUES 
             (1,1,1,'s1')
            ,(2,1,1,'s2')
            ,(3,2,1,'s2')
            ,(4,1,1,'s3')
            ,(6,3,2,'s3')
            ,(5,2,1,'s3')
            ;
6 rows affected

WITH cte AS 
(
SELECT *,
       COUNT(info) OVER (PARTITION BY info) total_count,
       SUM(CASE WHEN opid=1 AND opvalue=1 THEN 1 END) OVER (PARTITION BY info) conditional_count
FROM product
)
SELECT DISTINCT id, opid, opvalue, info
FROM cte
WHERE total_count = conditional_count;


id
opid
opvalue
info


1
1
1
s1