From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
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 11:54:07 -0400
Message-ID: <c1c41715-326d-3649-8b1d-918bfdbc9831@sqlexec.com> (raw)
In-Reply-To: <87tv8evuq0.fsf@news-spur.riddles.org.uk>
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>
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.
>
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: MichaelDBA@sqlexec.com, andrew@tao11.riddles.org.uk, jeff.janes@gmail.com, pryzby@telsasoft.com, ivoras@gmail.com
Subject: Re: Optimising a two column OR check
In-Reply-To: <c1c41715-326d-3649-8b1d-918bfdbc9831@sqlexec.com>
* 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