From: | Ivan Voras <ivoras(at)gmail(dot)com> |
---|---|
To: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Optimising a two column OR check |
Date: | 2019-10-13 13:38:49 |
Message-ID: | CAF-QHFWryzwqKcV2DrvCwwx-1HuSpRW=weVgUNO2gKop6LZc6A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sat, 12 Oct 2019 at 17:46, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:
> >>>>> "MichaelDBA" == MichaelDBA <MichaelDBA(at)sqlexec(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | dangal | 2019-10-14 01:27:35 | pg_stat_bgwriter |
Previous Message | Ivan Voras | 2019-10-13 13:29:19 | Re: Optimising a two column OR check |