"David G. Johnston" <[email protected]> writes:
> On Thursday, April 23, 2020, Thomas Kellerer <[email protected]> wrote:
>> Plus: scanning idx_tabla_entidad is more efficient because that index is
>> smaller.
> Really? The absence of 33 million rows in the partial index seems like it
> would compensate fully and then some for the extra included columns.
On the other hand, an indexscan is likely to end up being effectively
random-access rather than the purely sequential access involved in
a seqscan. (If the index was built recently, then it might not be
so bad --- but the planner doesn't know that, so it assumes that the
index leaf pages are laid out pretty randomly.) Moreover, unless the
table is mostly marked all-visible, there will be another pile of
randomized accesses into the heap to validate visibility of the index
entries.
Bottom line is that this choice is not nearly as open-and-shut as
the OP seems to think. In fact, it's fairly likely that this is a
badly designed index, not a well-designed one that the planner is
unaccountably failing to use. Both covering indexes and partial
indexes are easily-misused features that can make performance worse
not better.
regards, tom lane
