Re: PostgreSQL does not choose my indexes well

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Thursday, April 23, 2020, Thomas Kellerer <shammat(at)gmx(dot)net> 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