Re: Optimising a two column OR check

>>>>> "Ivan" == Ivan Voras <ivoras(at)gmail(dot)com> writes:

Ivan> Hello,
Ivan> There's a "users" table with the following structure:

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

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

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

Ivan> And I'm running this query:

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

To get friends of user 42:

SELECT user1_id FROM friend WHERE user2_id=42
UNION ALL
SELECT user2_id FROM friend WHERE user1_id=42;

assuming you create the (user2_id,user1_id) index, this should get you
an Append of two index-only scans. We can use UNION ALL here rather than
UNION because the table constraints ensure there are no duplicates.

--
Andrew (irc:RhodiumToad)