From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Ivan Voras <ivoras@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Optimising a two column OR check
Date: Sat, 12 Oct 2019 16:46:44 +0100
Message-ID: <87tv8evuq0.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <25d9306a-d0e9-ed82-3128-bff469f09aed@sqlexec.com>
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>
>>>>> "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.
--
Andrew (irc:RhodiumToad)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@tao11.riddles.org.uk, MichaelDBA@sqlexec.com, jeff.janes@gmail.com, pryzby@telsasoft.com, ivoras@gmail.com
Subject: Re: Optimising a two column OR check
In-Reply-To: <87tv8evuq0.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox