On Fri, Apr 24, 2020 at 2:33 PM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Jeff Janes (jeff.janes@gmail.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.
