Re: PostgreSQL does not choose my indexes well

On Fri, Apr 24, 2020 at 2:33 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Jeff Janes (jeff(dot)janes(at)gmail(dot)com) wrote:
> > In order to read 1409985 / 12839 = 109 rows per buffer page, the table
> must
> > be extraordinarily well clustered on this index. That degree of
> clustering
> > is going to steal much of the thunder from the index-only scan. But in
> my
> > hands, it does still prefer the partial index with index-only scan by a
> > cost estimate ratio of 3 to 1 (despite it actually being slightly slower)
> > so I don't know why you don't get it being used.
>
> Turns out to be because what was provided wasn't actually what was being
> used- there's a domain in there and that seems to gum up the works and
> make it so we don't consider the partial index as being something we can
> use (see the discussion at the end of the other sub-thread).
>

Thanks. I somehow managed to overlook the existence of the entire last 24
hours of discussion. But if I change the type of entidad.cod_tabla to
match the domain now shown in table.cod_table, I can still get the index
only scan over the partial index. Now the cost estimate has changed so it
slightly prefers the other index instead (in agreement with the original
report) but usage of the partial index-only can is still possible (e.g. if
I drop the single column full-table index). I don't understand why the
domain changes the estimate without changing the execution, but it isn't
something that is very important to me. I'm more interested in the index
only scan is not actually much if any faster. Even if there is no IO
benefit due to the clustering, I'd still expect there to be some CPU
benefit of not jumping back and forth between index pages and heap pages,
but iI don't know how much effort it is worth to put into that either.

Cheers,

Jeff