On Thu, Apr 23, 2020 at 8:29 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:
> On Thursday, April 23, 2020, Thomas Kellerer <shammat@xxxxxxx> 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.
