Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iJJcU-0006V8-UP for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:47:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iJJbV-00043i-Nz for pgsql-performance@arkaria.postgresql.org; Sat, 12 Oct 2019 15:46:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iJJbV-00043J-DP for pgsql-performance@lists.postgresql.org; Sat, 12 Oct 2019 15:46:49 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iJJbT-0002uU-6N for pgsql-performance@postgresql.org; Sat, 12 Oct 2019 15:46:49 +0000 Received: from [192.168.127.1] (port=12669 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtp (Exim 4.92.3 (FreeBSD)) (envelope-from ) id 1iJJbQ-0009PH-7F; Sat, 12 Oct 2019 15:46:44 +0000 Received: from localhost ([127.0.0.1]:34655 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.92.1 (FreeBSD)) (envelope-from ) id 1iJJbQ-0009HQ-2j; Sat, 12 Oct 2019 15:46:44 +0000 From: Andrew Gierth To: MichaelDBA Cc: Jeff Janes , Justin Pryzby , Ivan Voras , postgres performance list Subject: Re: Optimising a two column OR check In-Reply-To: <25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com> (MichaelDBA@sqlexec.com's message of "Sat, 12 Oct 2019 11:35:22 -0400") Message-ID: <87tv8evuq0.fsf@news-spur.riddles.org.uk> References: <20191012144336.GA4475@telsasoft.com> <87y2xqvv1x.fsf@news-spur.riddles.org.uk> <25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.2 (berkeley-unix) Date: Sat, 12 Oct 2019 16:46:44 +0100 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "MichaelDBA" == MichaelDBA writes: MichaelDBA> Nope, vacuumed it and still got the bitmap index scans. Let's see your explains. Here's mine: # set enable_seqscan=false; -- because I only have a few rows SET # insert into friend values (1,2),(2,5); INSERT 0 2 # vacuum analyze friend; VACUUM # explain analyze SELECT user1_id FROM friend WHERE user2_id=2 UNION ALL select user2_id FROM friend WHERE user1_id=2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Append (cost=0.13..8.32 rows=2 width=4) (actual time=0.009..0.014 rows=2 loops=1) -> Index Only Scan using friend_user2_id_user1_id_idx on friend (cost=0.13..4.15 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1) Index Cond: (user2_id = 2) Heap Fetches: 0 -> Index Only Scan using friend_pkey on friend friend_1 (cost=0.13..4.15 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1) Index Cond: (user1_id = 2) Heap Fetches: 0 Planning Time: 0.271 ms Execution Time: 0.045 ms (9 rows) Note that you have to put some actual rows in the table; if it is completely empty, you'll not get a representative result. -- Andrew (irc:RhodiumToad)