On Sat, 12 Oct 2019 at 17:46, Andrew Gierth <and...@tao11.riddles.org.uk> wrote:
> >>>>> "MichaelDBA" == MichaelDBA <michael...@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. > Confirming what's been said - the whole thing works fine on 10. I can't get index only scans on 9.6, but that's a dev machine anyway. Now if only hash indexes supported multiple column, that'd probably result in all my data being returned from a single read of a hash bucket, but that's going into microoptimisation territory :) Thanks!
