From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Thomas Kellerer <shammat@gmx.net>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL does not choose my indexes well
In-reply-to: 
 <CAKFQuwY9twbudMDvmuo+9Mjy0y-cJcvzPazD07-oB+WV1qOMnA@mail.gmail.com>
References: <35dc90d6-e0c2-13b8-3184-e3db5d854a78@gmail.com>
 <1bb37a19-77f6-8c49-073e-ed6251c98583@gmx.net>
 <CAKFQuwY9twbudMDvmuo+9Mjy0y-cJcvzPazD07-oB+WV1qOMnA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Thu, 23 Apr 2020 06:57:29 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4314.1587655779.1@sss.pgh.pa.us>
Date: Thu, 23 Apr 2020 11:29:39 -0400
Message-ID: <4315.1587655779@sss.pgh.pa.us>
Precedence: bulk

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