Re: PostgreSQL does not choose my indexes well

On Thu, Apr 23, 2020 at 8:29 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "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.
>

I feel like I'm missing something as the OP's query is choosing indexscan -
just it is choosing to scan the full index containing the searched upon
field instead of a partial index that doesn't contain the field but whose
predicate matches the where condition - in furtherance of a count(*)
computation where the columns don't really matter.

I do get "its going to perform 1.4 million random index entries and heap
lookup anyway - so it doesn't really matter" - but the first answer was
"the full index is smaller than the partial" which goes against my
intuition.

The sequential scan that isn't being used would have to touch 25x the
number of records - so its non-preference seems reasonable.

David J.