Postgres 9.6

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 change_log
(
    id               serial      not null
        constraint pk_change_log
            primary key,
    change           jsonb       not null,
    previous         jsonb,
    changed_at       timestamp with time zone default timezone('utc'::text, now()),
    specific_changes jsonb
);

INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (1, '{"val": 2, "test": "test", "nested": {"nval": 1}}', 'null', '2020-11-12 16:53:28.827896', '{"val2": "Value2"}');
INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (2, '{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}', '{"val": "2", "test": "testChange", "nested": {"nval": 1}}', '2020-11-15 12:18:35.021843', '{"new": "testValue"}');
INSERT INTO public.change_log (id, change, previous, changed_at, specific_changes) VALUES (3, '{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}', '{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}', '2020-11-15 12:19:40.832843', '{"new": "testChange", "nested": {"val": 1}}');
1 rows affected

1 rows affected

1 rows affected

select *
from change_log
where exists (
    select 1
    from jsonb_each_text(change) as t(k,v)
    where v ilike '%change%');


id
change
previous
changed_at
specific_changes


2
{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}
{"val": "2", "test": "testChange", "nested": {"nval": 1}}
2020-11-15 12:18:35.021843+00
{"new": "testValue"}

3
{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}
{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}
2020-11-15 12:19:40.832843+00
{"new": "testChange", "nested": {"val": 1}}


select *
from change_log
where
    exists (
        select 1
        from jsonb_each_text(nullif(change, 'null')) as t(k,v)
        where v ilike '%change%') or
    exists (
        select 1
        from jsonb_each_text(nullif(previous, 'null')) as t(k,v)
        where v ilike '%change%') or
    exists (
        select 1
        from jsonb_each_text(nullif(specific_changes, 'null')) as t(k,v)
        where v ilike '%change%');


id
change
previous
changed_at
specific_changes


2
{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}
{"val": "2", "test": "testChange", "nested": {"nval": 1}}
2020-11-15 12:18:35.021843+00
{"new": "testValue"}

3
{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}
{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}
2020-11-15 12:19:40.832843+00
{"new": "testChange", "nested": {"val": 1}}


select *
from change_log
where
    exists (
        select 1 from (
            select * from jsonb_each_text(nullif(change, 'null')) union all
            select * from jsonb_each_text(nullif(previous, 'null')) union all
            select * from jsonb_each_text(nullif(specific_changes, 'null'))) as t(k,v)
        where t.v ilike '%change%');


id
change
previous
changed_at
specific_changes


2
{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}
{"val": "2", "test": "testChange", "nested": {"nval": 1}}
2020-11-15 12:18:35.021843+00
{"new": "testValue"}

3
{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}
{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}
2020-11-15 12:19:40.832843+00
{"new": "testChange", "nested": {"val": 1}}

id	change	previous	changed_at	specific_changes
2	{"val": "testNewChange", "test": "testChange", "nested": {"key": 1}}	{"val": "2", "test": "testChange", "nested": {"nval": 1}}	2020-11-15 12:18:35.021843+00	{"new": "testValue"}
3	{"val": "newewChange", "test": "changeNew", "nested": {"val": 3}}	{"val": "testNewChange", "test": "testValue", "nested": {"key": 1}}	2020-11-15 12:19:40.832843+00	{"new": "testChange", "nested": {"val": 1}}