Re: Optimising a two column OR check

On Sat, Oct 12, 2019 at 10:43 AM Justin Pryzby <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