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?