Re: [GENERAL] index duplicates primary key, but is used more?

jonathan vanasco <postgres@2xlp.com> writes:
> What I noticed when checking stats earlier, is that although `idx_test_foo_id_asc` is the same as the PKEY... it was
usedabout 10x more than the pkey. 

> Does anyone know of this is just random (perhaps due to the name being sorted earlier) or there is some other reason
thatindex would be selected ? 

It's almost certainly just an artifact.  The planner considers a table's
indexes in OID order.  I don't recall offhand whether it would keep the
first or last of a series of identical-cost plans, but it'd be one or the
other of those behaviors; it would not continue to consider both indexes
once it noticed the plans were the same.

One thing that could favor a newer index is that it probably has somewhat
less bloat in it, resulting in a fractionally smaller cost estimate.  This
doesn't make it better in any absolute sense; reindexing the older index
would reverse that preference, at least for a time.

            regards, tom lane