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 analytics 
(
    id bigint NOT NULL,
    col_json json,
    CONSTRAINT clbk_logs_pkey PRIMARY KEY (id)
);

CREATE TABLE


insert into analytics (id, col_json)
values 
  (1, '{
    "City": "Fontana", 
    "Timezone": "America/Los_Angeles",
    "Longitude": "-117.4864123",
    "Timestamp": "2020-07-15T12:13:00-07:00",
    "refs": ["123", "456", "789"], "tZone": "PPP"
}'),
  (2, '{
    "City": "Fontana", 
    "Timezone": "America/Los_Angeles",
    "Longitude": "-117.4864123",
    "Timestamp": "2020-07-15T12:13:00-07:00",
    "refs": ["42", "41", "100"], "tZone": "PPP"
}');

INSERT 0 2


select a.*
from analytics a
where col_json::jsonb -> 'refs' ?| array['123','pqa','bhu','qwerty'];


id
col_json


1
{
    "City": "Fontana", 
    "Timezone": "America/Los_Angeles",
    "Longitude": "-117.4864123",
    "Timestamp": "2020-07-15T12:13:00-07:00",
    "refs": ["123", "456", "789"], "tZone": "PPP"
}



SELECT 1


select a.*
from analytics a
where exists (select * 
              from json_array_elements_text(a.col_json -> 'refs') as x(item) 
              where x.item in ('123','pqa','bhu','qwerty'));


id
col_json


1
{
    "City": "Fontana", 
    "Timezone": "America/Los_Angeles",
    "Longitude": "-117.4864123",
    "Timestamp": "2020-07-15T12:13:00-07:00",
    "refs": ["123", "456", "789"], "tZone": "PPP"
}



SELECT 1


select a.*
from analytics a
where a.col_json::jsonb -> 'refs' @> '["123", "456"]';


id
col_json


1
{
    "City": "Fontana", 
    "Timezone": "America/Los_Angeles",
    "Longitude": "-117.4864123",
    "Timestamp": "2020-07-15T12:13:00-07:00",
    "refs": ["123", "456", "789"], "tZone": "PPP"
}



SELECT 1


select a.*
from analytics a
where a.col_json::jsonb -> 'refs' @> '["123", "500"]';


id
col_json



SELECT 0