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 <pry...@telsasoft.com
<mailto:pry...@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
