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 <[email protected] 
>> <mailto:[email protected]>> 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
>