Postgres 10

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 people( name VARCHAR(100), interests JSONB )

CREATE TABLE


INSERT INTO people VALUES
('Mary','["swimming","reading","jogging"]'),
('John','["climbing","reading"]'),
('Ann','["swimming","watching TV","programming"]'),
('Carl','["knitting"]')

INSERT 0 4


SELECT name, count / sum(count) over () AS ratio
  FROM(
SELECT name, COUNT(name) AS count
  FROM people 
  JOIN jsonb_array_elements(interests) AS j(elm) ON TRUE
 WHERE interests @> 
       ANY (ARRAY ['"reading"', '"swimming"', '"knitting"', '"cars"']::jsonb[])
 GROUP BY name ) q 


name
ratio


Ann
0.33333333333333333333

Carl
0.11111111111111111111

John
0.22222222222222222222

Mary
0.33333333333333333333



SELECT 4

name	ratio
Ann	0.33333333333333333333
Carl	0.11111111111111111111
John	0.22222222222222222222
Mary	0.33333333333333333333