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 same for indexes we care about...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
