Re: Optimising a two column OR check

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.009rows=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.004rows=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.
>