Optimising a two column OR check

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux