Postgres 16

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

INSERT 0 4

SELECT brand_group, p.*
FROM  (
   SELECT CASE descriptor
            WHEN 'Fast Food'    THEN 'Base'
            WHEN 'Sit Down'     THEN 'Premium'
            WHEN 'Bar'          THEN 'Premium'
            WHEN 'Eatery'       THEN 'Premium'
            WHEN 'Full Service' THEN 'Deluxe'
            WHEN 'Boutique'     THEN 'Deluxe'
          END AS brand_group
        , count(*) FILTER (WHERE accuracy_of_bill_yn = 'Yes') AS a_ct
        , count(*) FILTER (WHERE cleanliness_yn = 'Yes')      AS c_ct
        , count(*) FILTER (WHERE service_yn = 'Yes')          AS s_ct
        , count(*) FILTER (WHERE value_yn = 'Yes')            AS v_ct
   FROM   surveys
/* -- not in my minimum table  
   WHERE  responsedate >= now() - interval '12 months'
-- AND    surveyid NOT IN (SELECT surveyid FROM surveys)  -- ?? nonsense
   AND    region IN (1,2,3,4,5)
   AND    brand_group IS NOT NULL
*/  
   GROUP  BY 1
   ) sub
CROSS  JOIN LATERAL (
   VALUES
     ('Accuracy of bill', a_ct)
   , ('Cleanliness'     , c_ct)
   , ('Service'         , s_ct)
   , ('Value'           , v_ct)
   ) p(base, count)
ORDER  BY 1;  -- need that?

brand_group	base	count
Base	Accuracy of bill	1
Base	Cleanliness	1
Base	Service	0
Base	Value	0
Deluxe	Accuracy of bill	0
Deluxe	Cleanliness	0
Deluxe	Service	1
Deluxe	Value	1
Premium	Accuracy of bill	0
Premium	Cleanliness	2
Premium	Service	1
Premium	Value	0

SELECT 12