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

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(at)2xlp(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>