Subject: Re: Optimising a two column OR check
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Jeff Janes <jeff.janes@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>,
 Ivan Voras <ivoras@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
References: 
 <CAF-QHFXQduEoX=ADZh2QYECLdcM1zOuY6=K0cE_DBjhdH3v+WA@mail.gmail.com>
 <20191012144336.GA4475@telsasoft.com>
 <CAMkU=1w1F0t7c78mJMF7DdBbk50aGy3-AROGzcbdPrS0ApmGAA@mail.gmail.com>
 <a0ab3bae-6a5d-b2ea-b3a0-cbcff72f394f@sqlexec.com>
 <87y2xqvv1x.fsf@news-spur.riddles.org.uk>
 <25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com>
 <87tv8evuq0.fsf@news-spur.riddles.org.uk>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <c1c41715-326d-3649-8b1d-918bfdbc9831@sqlexec.com>
Date: Sat, 12 Oct 2019 11:54:07 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.5.2
MIME-Version: 1.0
In-Reply-To: <87tv8evuq0.fsf@news-spur.riddles.org.uk>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Yep, you're right, Andrew, adding a couple rows made it do the index 
only scan.  I reckon I got misled by turning off sequential scans, 
thinking that actual rows were not important anymore.  Overly simplistic 
reasonings can get one into trouble, lol.

Regards,
Michael Vitale


Andrew Gierth wrote on 10/12/2019 11:46 AM:
>>>>>> "MichaelDBA" == MichaelDBA  <MichaelDBA@sqlexec.com> 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.
>