Re: get_actual_variable_range vs idx_scan/idx_tup_fetch

On 10/19/14, 2:09 PM, Tom Lane wrote:
> 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.

Maybe a good alternative is:

ORDER BY int( table.reltuples / index.relpages / BLKSZ ) DESC, oid

By comparing on average tuple size throwing away the fraction presumably we'd throw away noise from page splits too.

We'd want to use table.reltuples for consistency sake, though theoretically in this case I'd think it should be the
samefor indexes we care about...
 
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com