Re: get_actual_variable_range vs idx_scan/idx_tup_fetch

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
> On 10/17/14, 10:16 PM, Tom Lane wrote:
>> BTW, on re-reading that code I notice that it will happily seize upon
>> the first suitable index ("first" in OID order), regardless of how many
>> lower-order columns that index has got. This doesn't make any difference
>> I think for get_actual_variable_range's own purposes, because it's only
>> expecting to touch the endmost index page regardless. However, in light
>> of Marko's complaint maybe we should teach it to check all the indexes
>> and prefer the matching one with fewest columns?

> The real cost here isn't the number of columns, it's the size of the index, no? So shouldn't we look at relpages instead? For example, you'd certainly want to use an index on (field_we_care_about, smallint_field) over an index on (field_we_care_about, big_honking_text_field).

Yeah, perhaps. I'd been wondering about adding a tie-breaking rule,
but that's a much simpler way to think about it. OTOH, that approach
could result in some instability in the choice of index: if you've got
both (field_we_care_about, some_int_field) and (field_we_care_about,
some_other_int_field) then it might switch between choosing those two
indexes from day to day depending on basically-chance issues like when
page splits occur. That would probably annoy Marko even more than the
current behavior :-(, because it would scatter the planner's usage
across multiple indexes for no very good reason.

The coding I'd been imagining at first would basically break ties in
column count according to index OID order, so its choices would be stable
as long as you did not add/drop indexes. That seems like a good property
to try to preserve.

regards, tom lane