Thread: [GENERAL] index duplicates primary key, but is used more?
i'm doing a performance audit and noticed something odd. we tested a table a while back, by creating lots of indexes that match different queries (30+). for simplicity, here's a two column table: CREATE TABLE foo (id INT PRIMARY KEY value INT NOT NULL DEFAULT 0, ); The indexes were generated by a script, so we had things like: CREATE INDEX idx_test_foo_id_asc ON foo(id ASC); CREATE INDEX idx_test_foo_id_desc ON foo(id DESC); CREATE INDEX idx_test_foo_val_asc ON foo(value ASC); CREATE INDEX idx_test_foo_value_desc ON foo(value DESC); 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 ? my concern in deleting it, is that it might be preferred for queries due to hinting from the explicit 'order by' (even thoughthe contents are the same) and I may lose an index being leveraged in that query. It's on a GIANT table, so it would be hard to recreate.
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
Hello,
I suggest you log all execution plan in your db log through using auto_explain extension.
And then analyze the stats about your concerned indexes .
No hint syntax in Postgresql.
Which indexes and join method would be adopt all depend on optimizer whose behaviour is also affected by some parameters.
Steven
2017-06-03 7:02 GMT+08:00 jonathan vanasco <postgres@2xlp.com>:
i'm doing a performance audit and noticed something odd.
we tested a table a while back, by creating lots of indexes that match different queries (30+).
for simplicity, here's a two column table:
CREATE TABLE foo (id INT PRIMARY KEY
value INT NOT NULL DEFAULT 0,
);
The indexes were generated by a script, so we had things like:
CREATE INDEX idx_test_foo_id_asc ON foo(id ASC);
CREATE INDEX idx_test_foo_id_desc ON foo(id DESC);
CREATE INDEX idx_test_foo_val_asc ON foo(value ASC);
CREATE INDEX idx_test_foo_value_desc ON foo(value DESC);
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 ?
my concern in deleting it, is that it might be preferred for queries due to hinting from the explicit 'order by' (even though the contents are the same) and I may lose an index being leveraged in that query.
It's on a GIANT table, so it would be hard to recreate.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general