Re: Optimising a two column OR check

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(at)telsasoft(dot)com
>> <mailto:pryzby(at)telsasoft(dot)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
>