From: MichaelDBA <MichaelDBA@sqlexec.com>
To: 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>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: Optimising a two column OR check
Date: Sat, 12 Oct 2019 11:33:53 -0400
Message-ID: <b871806c-6ac4-d977-23ca-15c286561d3e@sqlexec.com> (raw)
In-Reply-To: <a0ab3bae-6a5d-b2ea-b3a0-cbcff72f394f@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>
Yikes, apologies to all, my wording is the opposite of what I meant!
Index only scans are preferred for infrequently updated ones, not
heavily updated ones where the visibility map is updated often.
Regards,
Michael Vitale
MichaelDBA wrote on 10/12/2019 11:27 AM:
> Another thing to consider is the visibility map. From what I
> understand, index only scans are preferred for heavily updated tables,
> not infrequently updated ones. Even though index only scans imply
> ONLY they really aren't in the sense that they may need to visit the
> Visibility Map for the heap. This can be costly and the planner may
> remove index only scan consideration if the VM has tuples that are not
> visible.
>
> BTW, to Andrew, the UNION ALL alternative still results in bitmap
> index scans from my testing.
>
> Regards,
> Michael Vitale
>
>
>
> Jeff Janes wrote on 10/12/2019 11:17 AM:
>> On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby <pryzby@telsasoft.com
>> <mailto:pryzby@telsasoft.com>> wrote:
>>
>> On Sat, Oct 12, 2019 at 04:39:56PM +0200, Ivan Voras wrote:
>> > With seqscan disabled, I get this plan on 9.6:
>> > Bitmap Heap Scan on friend (cost=8.42..19.01 rows=14 width=8)
>> ...
>> > I expected to get an index-only scan in this situation, as that
>> would be a
>> > very common query. Is there a way to actually make this sort of
>> query
>> > resolvable with an index-only scan? Maybe a different table
>> structure would
>> > help?
>>
>>
>> It would have to scan the entire index to find the cases where
>> user2_id=42 but user1_id is not constrained. Technically User1_id
>> would be constrained to be less than 42, but I don't think the
>> planner will take that into account.
>>
>>
>> The v11 release notes have this relevant item:
>>
>> https://www.postgresql.org/docs/11/release-11.html
>> |Allow bitmap scans to perform index-only scans when possible
>> (Alexander Kuzmenkov)
>>
>>
>> But this is not one of those cases. It is only possible when the
>> only data needed is whether the row exists or not.
>>
>> Cheers,
>>
>> Jeff
>
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, jeff.janes@gmail.com, pryzby@telsasoft.com, ivoras@gmail.com, andrew@tao11.riddles.org.uk
Subject: Re: Optimising a two column OR check
In-Reply-To: <b871806c-6ac4-d977-23ca-15c286561d3e@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