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