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

Поиск
Список
Период
Сортировка
От jonathan vanasco
Тема [GENERAL] index duplicates primary key, but is used more?
Дата
Msg-id 8D07ECA6-1B49-45AF-9B30-2FEF2360236C@2xlp.com
обсуждение исходный текст
Ответы Re: [GENERAL] index duplicates primary key, but is used more?
Re: [GENERAL] index duplicates primary key, but is used more?
Список pgsql-general
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.



В списке pgsql-general по дате отправления:

Предыдущее
От: Neil Anderson
Дата:
Сообщение: Re: [GENERAL] dump to pg
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] index duplicates primary key, but is used more?