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

собственно сабж про постгрис.

ну и вопросики:

1. имеем табличку

| id | col1 | col2 | col3 | ...

далее имеем раздельне индексы по col1, col2, col7

делаем выборку

SELECT
*
FROM
table
WHERE
col1 = 'abc'
AND col7 = 'cde'
AND col2 = 'fgh'

Вопрос будут ли использоваться в такой выборке все три индекса или
(как в MySQL) обязательно делать составной?

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%';
...

будет ли профит по использованию памяти/итп в таком случае или
наоборот будет больше оверхеда?
--

. ''`. 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

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

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.

> Но есть ещё один компромисс - индекс на выражение, например:
> CREATE UNIQUE INDEX first8bytes ON
> foo( lower(substring(name, 1, 8)) );
> При этом размер индекса будет сравним с индексом
> на столбец типа bigint, а индексация будет в 8 раз глубже.
>
PS. Здесь UNIQUE бессмысленно.
Если name уникально, то должен быть отдельный
уникальный индекс на этот столбец!

--
// Dmitriy.

>> SELECT
>> *
>> FROM
>> table
>> WHERE
>> col1 = 'abc'
>> AND col7 = 'cde'
>> AND col2 = 'fgh'

>> Вопрос будут ли использоваться в такой выборке все три индекса или
>> (как в MySQL) обязательно делать составной?

> Да, вероятно, в этом запросе будут использоваться
> все 3 индекса в отдельности. Однако по разным причинам,
> в частности, например, если добавить ORDER BY,
> планировщик может использовать лишь один индекс.

хгм.

> Многостолбцовый индекс будет эффективнее отдельных
> индексов, если в условиях выборки будут присутствовать
> все столбцы, входящие в индекс (или, обязательно,
> хотя бы *первые*).

ну это то понятно.

mysql не умел по двум индексам искать и потому приходилось каждый раз
думать какой индекс больше сужает поиск и делать вложенные запросы.

если ORDER BY нет, то все три индекса будут всегда использоваться?

где-то об этом написано (ну кроме RTFS?)

>> 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));

то есть чтобы использовался конкретный частичный индекс выражение в
WHERE обязано быть таким же как и во WHERE самого индекса.
и даже бОльшие уточнения 'aa%' вместо 'a%' уже этот индекс
отбрасывают?

--

. ''`. 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

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

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

>
> где-то об этом написано (ну кроме RTFS?)
>
Это специфика Postgres, поэтому достовернее, чем
в разделе 11.5 информации нигде быть не может.

>
>
> >> 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));
>
> то есть чтобы использовался конкретный частичный индекс выражение в
> WHERE обязано быть таким же как и во WHERE самого индекса.
> и даже бОльшие уточнения 'aa%' вместо 'a%' уже этот индекс
> отбрасывают?
>
Правильно!

--
// Dmitriy.