Optimising a two column OR check

From: Ivan Voras <ivoras(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Optimising a two column OR check
Date: 2019-10-12 14:39:56
Message-ID: CAF-QHFXQduEoX=ADZh2QYECLdcM1zOuY6=K0cE_DBjhdH3v+WA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

There's a "users" table with the following structure:

CREATE TABLE "user" (
id SERIAL PRIMARY KEY,
-- other fields
);

and there's a "friends" table with the following structure:

CREATE TABLE friend (
user1_id INTEGER NOT NULL REFERENCES "user"(id),
user2_id INTEGER NOT NULL REFERENCES "user"(id),
-- other fields
CHECK (user1_id < user2_id),
PRIMARY KEY (user1_id, user2_id)
);

And I'm running this query:

SELECT user1_id,user2_id FROM friend WHERE user1_id=42 OR user2_id=42;

With seqscan disabled, I get this plan on 9.6:

QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on friend (cost=8.42..19.01 rows=14 width=8)
Recheck Cond: ((user1_id = 1) OR (user2_id = 2))
-> BitmapOr (cost=8.42..8.42 rows=14 width=0)
-> Bitmap Index Scan on friend_pkey (cost=0.00..4.21 rows=7
width=0)
Index Cond: (user1_id = 1)
-> Bitmap Index Scan on friend_user2_id_user1_id_idx
(cost=0.00..4.21 rows=7 width=0)
Index Cond: (user2_id = 2)
(7 rows)

I expected to get an index-only scan in this situation, as that would be a
very common query. Is there a way to actually make this sort of query
resolvable with an index-only scan? Maybe a different table structure would
help?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2019-10-12 14:43:36 Re: Optimising a two column OR check
Previous Message Jeff Janes 2019-10-11 01:08:54 Re: Modification of data in base folder and very large tables