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 t 
(
 id         SERIAL  primary key, 
 js         JSONB
)
INSERT INTO t(js) VALUES
('{ "categories" : ["economic", "Social"], "tags": ["world" ,"eco-friendly"] }'),     
('{ "categories" : ["economic"], "tags": ["eco-friendly"] , "keywords" : ["2050"] }')
2 rows affected

SELECT key, value
  FROM ( SELECT j.key, jj.value, 
                RANK() OVER (PARTITION BY j.key ORDER BY COUNT(*) DESC)
           FROM t,
        LATERAL jsonb_each(js) AS j,
        LATERAL jsonb_array_elements_text(j.value) AS jj
          GROUP BY j.key, jj.value ) AS q
 WHERE rank = 1


key
value


categories
economic

keywords
2050

tags
eco-friendly