1- and 2-dimensional indexes on same column: why is the 2d one preferred?

Поиск
Список
Период
Сортировка
От Marinos Yannikos
Тема 1- and 2-dimensional indexes on same column: why is the 2d one preferred?
Дата
Msg-id 49C7FD8D.6050905@geizhals.at
обсуждение исходный текст
Ответы Re: 1- and 2-dimensional indexes on same column: why is the 2d one preferred?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Recent versions of PostgreSQL seem to prefer 2d indexes somehow:

for a table "foo" with
"i_a" btree (a)
"i_ab" btree (a, b)

SELECT * FROM foo WHERE a=123
will often use "i_ab" and not "i_a" (even right after ANALYZE). This
raises some questions:

- is there even any benefit in still having both these indexes? (can
some operations still use "i_a" only or is "i_ab" always a sufficient
replacement for "i_a"?)

- is this even working as intended? in my experience (can't back it up
with numbers atm.), 2-dimensional indexes are often slower and they
degrade noticeably over time. Without knowing the implementation, I'd
assume that using "i_ab" would usually require more page fetches than
using "i_a" for the above query.

Regards,
  Marinos



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

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: unexpected check constraint violation
Следующее
От: "Harvey, Allan AC"
Дата:
Сообщение: Re: LISTEN/NOTIFY problem