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 params (
    id serial primary key,
    a_key text,
    a_value text,
    visit_id integer
);

create table visits (
    id serial primary key,
    name text,
    visitor_id integer
);

insert into visits (name, visitor_id) values
  ('Visited Page', 1)
, ('Visited Page', 2);

insert into params (a_key, a_value, visit_id) values
  ('key1', 'value1', 1)
, ('key2', 'value2', 1)
, ('key3', 'value3', 1)
, ('key1', 'value1', 2)
, ('key2', 'value2', 2);
2 rows affected

5 rows affected

-- solution
SELECT v.*
FROM   params p1
JOIN   params p3 USING (visit_id)
JOIN   visits v  ON v.id = p3.visit_id
WHERE  p1.a_key = 'key1' AND p1.a_value = 'value1'
AND    p3.a_key = 'key3' AND p3.a_value = 'value3';


id
name
visitor_id


1
Visited Page
1


-- your old query
select *
from visits
right join params as p on p.visit_id = visits.id
where a_key = 'key1' and a_value = 'value1';


id
name
visitor_id
id
a_key
a_value
visit_id


1
Visited Page
1
1
key1
value1
1

2
Visited Page
2
4
key1
value1
2