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 connections  (user_id text, other_user_id text);

CREATE TABLE


insert into connections
values
('foo','bar'),
('baz','asdf');

INSERT 0 2


create table events (user_id text, payload jsonb);

CREATE TABLE


insert into events
values
('foo', '{
  "otherUser": {
    "id": "bar"
  }
}'
),
('baz', '{
  "otherUser": {
    "id": "asdf"
  }           
}');

INSERT 0 2


SELECT * 
FROM events ev 
  JOIN connections con 
    ON con.user_id = ev.user_id 
   AND con.other_user_id = ev.payload -> 'otherUser' ->> 'id';


user_id
payload
user_id
other_user_id


baz
{"otherUser": {"id": "asdf"}}
baz
asdf

foo
{"otherUser": {"id": "bar"}}
foo
bar



SELECT 2


SELECT * 
FROM events ev 
  JOIN connections con 
    ON con.user_id = ev.user_id 
   AND con.other_user_id = ev.payload #>> '{otherUser,id}';


user_id
payload
user_id
other_user_id


baz
{"otherUser": {"id": "asdf"}}
baz
asdf

foo
{"otherUser": {"id": "bar"}}
foo
bar



SELECT 2


SELECT * 
FROM events ev 
  JOIN connections con 
    ON con.user_id = ev.user_id 
   AND con.other_user_id = jsonb_extract_path_text(ev.payload, 'otherUser','id');


user_id
payload
user_id
other_user_id


baz
{"otherUser": {"id": "asdf"}}
baz
asdf

foo
{"otherUser": {"id": "bar"}}
foo
bar



SELECT 2

user_id	payload	user_id	other_user_id
baz	{"otherUser": {"id": "asdf"}}	baz	asdf
foo	{"otherUser": {"id": "bar"}}	foo	bar