Re: [pgsql-ru-general] А что почитать про индексы?

Приветствую,

12 ноября 2011 г. 1:05 пользователь Dmitry E. Oboukhov
<unera(at)debian(dot)org>написал:

> собственно сабж про постгрис.
>
> ну и вопросики:
>
> 1. имеем табличку
>
> | id | col1 | col2 | col3 | ...
>
> далее имеем раздельне индексы по col1, col2, col7
>
> делаем выборку
>
> SELECT
> *
> FROM
> table
> WHERE
> col1 = 'abc'
> AND col7 = 'cde'
> AND col2 = 'fgh'
>
>
> Вопрос будут ли использоваться в такой выборке все три индекса или
> (как в MySQL) обязательно делать составной?
>
Да, вероятно, в этом запросе будут использоваться
все 3 индекса в отдельности. Однако по разным причинам,
в частности, например, если добавить ORDER BY,
планировщик может использовать лишь один индекс.
Многостолбцовый индекс будет эффективнее отдельных
индексов, если в условиях выборки будут присутствовать
все столбцы, входящие в индекс (или, обязательно,
хотя бы *первые*).
Вообще, в данном случае, можно создать все 4 индекса.
Но, понятно, что это доп. место на диске, и, если таблица
часто меняется, доп. затраты времени выполнения на
обновление индексов.

>
>
> 2. Имеется таблица с текстовым полем
>
> | id | keyword | col1 | col2 | ...
>
>
> keyword вообще говоря уникален, но не суть.
>
> нужен поиск вида
>
> WHERE
> keyword like 'что-то%';
>
> Но таблица несколько сот миллионов строк.
>
> какой индекс лучше построить в данном случае?
>
> можно ли построить несколько частичных индексов чтобы Pg автоматом
> использовал тот который больше подходит? будет ли иметь это смысл?
>
> то есть если я построю 26 индексов вида
>
> CREATE UNIQUE INDEX "name_a" ON "table" ("keyword")
> WHERE "keyword" like 'a%';
> CREATE UNIQUE INDEX "name_b" ON "table" ("keyword")
> WHERE "keyword" like 'b%';
> ...
>
> будет ли профит по использованию памяти/итп в таком случае или
> наоборот будет больше оверхеда?
>
Во-первых, каждый из этих 26-ти индексов будет использоваться
только при поиске по выражению вида C%, где C - [a-z], т.е.
только по паттерну, состоящим из одной первой буквы, т.е.
SELECT * FROM foo WHERE keyword LIKE 'ab%';
использовать такой индекс не будет.
Во-вторых, чем меньше индекс, тем меньше памяти
требуется для его обработки.
Но есть ещё один компромисс - индекс на выражение, например:
CREATE UNIQUE INDEX first8bytes ON
foo( lower(substring(name, 1, 8)) );
При этом размер индекса будет сравним с индексом
на столбец типа bigint, а индексация будет в 8 раз глубже.

Использование:
SELECT * FROM foo WHERE lower(substring(name, 1, 8)) =
lower(substring('dima', 1, 8));

--
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>

--
// Dmitriy.