From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jonathan vanasco <postgres(at)2xlp(dot)com> |
Cc: | pgsql-general general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: index duplicates primary key, but is used more? |
Date: | 2017-06-02 23:11:51 |
Message-ID: | 31354.1496445111@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
jonathan vanasco <postgres(at)2xlp(dot)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 used about 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 that index 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
From | Date | Subject | |
---|---|---|---|
Next Message | Steven Chang | 2017-06-04 04:36:56 | Re: index duplicates primary key, but is used more? |
Previous Message | jonathan vanasco | 2017-06-02 23:02:27 | index duplicates primary key, but is used more? |