From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Ivan Voras <ivoras@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Optimising a two column OR check
In-Reply-To: 
 <CAF-QHFXQduEoX=ADZh2QYECLdcM1zOuY6=K0cE_DBjhdH3v+WA@mail.gmail.com>
	(Ivan Voras's message of "Sat, 12 Oct 2019 16:39:56 +0200")
Message-ID: <87a7a6xamw.fsf@news-spur.riddles.org.uk>
References: 
 <CAF-QHFXQduEoX=ADZh2QYECLdcM1zOuY6=K0cE_DBjhdH3v+WA@mail.gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.2 (berkeley-unix)
Date: Sat, 12 Oct 2019 16:16:50 +0100
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "Ivan" == Ivan Voras <ivoras@gmail.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)