Обсуждение: Re: [pgsql-ru-general] Снова подниму вопрос: как заставить pg использовать НУЖНЫЙ индекс?

> по-хорошему надо explain analyze приводить, хотя я подозреваю, что оценка
> стоимости для таких индексов не очень хороша.

Дык я ж и привел EXPLAIN ANALYZE

> Могу посоветовать поиграться с нашим http://www.sai.msu.su/~megera/wiki/
> plantuner

> : <http://www.sai.msu.su/~megera/wiki/plantuner">http://www.sai.msu.su/
> ~megera/wiki/plantuner

--

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

>> по-хорошему надо explain analyze приводить, хотя я подозреваю, что оценка
>>> стоимости для таких индексов не очень хороша.

>> Дык я ж и привел EXPLAIN ANALYZE

> Надо привести explain запроса, когда используется правильный индекс.

вот

> EXPLAIN ANALYZE SELECT
    *
FROM
    "orders" "o"
WHERE

        "o"."status" IN ('confirm', 'accept', 'driving', 'waiting', 'transporting')


        AND "o"."gid" = '1'
        AND "o"."sid" = '147'
;

 Index Scan using edispatcher_orders_service_idx on orders o  (cost=0.28..9592.12 rows=2449 width=1867) (actual
time=0.031..0.099rows=5 loops=1) 
   Index Cond: ((gid = 1) AND (sid = 147))
 Total runtime: 0.197 ms
(3 строки)

Видно что он в ~2000 раз быстрее выполняется.
Но чтобы использовался этот индекс приходится удалить один из двух других
которые Pg хочет использовать в EXPLAIN приведенном в предыдущем письме.

--

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

> Олег, а смысл? Ну будет 1 индекс скан — и всё.
> Тут проблема в том, что есть уже btree по (gid, sid, ...), полный и при
> построении частичного планнер его почему-то видеть не хочет.

> Можно попробовать нечто совсем ненаучное, чтобы заставить частичный индекс
> заработать, — поправить запрос:

> "o"."status" IN ('confirm', 'accept', 'driving', 'waiting', 'transporting',
> 'smthnotexisting')


> И в частичный индекс тоже 'smthnotexisting' засунуть.

спасибо, интересная мысль.

я некоторые запросы заставлял использовать индекс прописывая помимо
WHERE еще и ORDER BY как в индексе. Иногда помогает ему выбрать нужный
индекс. Но если он выберет не тот, то ORDER BY усугублять будет
ситуацию.

ща посмотрю можно ли собрать хинт к планеру для моего постгриса.


--

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

> Я бы ещё крепко задумался о снижении количества индексов. Например, сам по себе
> "driver_work_index" btree (did, status)
> WHERE status = ANY (ARRAY['confirm'::text, 'accept'::text,
> 'driving'::text, 'waiting'::text, 'transporting'::text])

Имеется водитель. он видит свои заказы
у него
    WHERE
            did = ?
        AND status IN (..)
    ORDER BY
        status;

соответственно для его запроса построен индекс.
допустим ORDER BY можно убрать или оставить но убрать в индексе.
Но число индексов от этого не поменяется.

Далее имеется наблюдатель (диспетчер) у него

WHERE
        gid = ?
    AND sid = ?
    AND status IN (...)

и имеется другой наблюдатель

WHERE
        gid = ?
    AND status IN (...)

соответственно для водителя один индекс, для обоих наблюдателей
другой.

оба индекса частичные, занимают очень немного (то есть содержат в себе
максимум 500-1000 записей в работе)


> — странный. Зачем пихать status вторым слоем,

ORDER BY еще там есть в запросах. Впрочем можно убрать. вопрос в другом.
почему имея точный pattern индекса и WHERE условия Pg принимает
решение работать по двум индексам вместо одного точно подходящего и из
за этого получаем в 2000 раз меньшую производительность.


> Индекс по (gid, sid, did, booking_time) — выглядит монстрообразно. Опять же,
> есть серьёзное подозрение, что 3й и 4й слои не так уж и нужны.

Это для отчетов.
Там WHERE такой:

WHERE
        gid = ?
    AND sid = ?
    AND did = ?
    AND booking_time BETWEEN ? AND ?

> В общем, главных слова, как обычно два — кардинальность и селективность.

я смотрел, отказаться от каких-то индексов мне сложно: они изначально
затачивались под конкретное рабочее место.

есть запрос с таким WHERE значит под него строим индекс. По
возможности частичный.

что не так в этом подходе?

--

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

>
> > Надо привести explain запроса, когда используется правильный индекс.
> вот
> > EXPLAIN ANALYZE SELECT
...
> Index Scan using edispatcher_orders_service_idx on orders o (cost=0.28..9592.12 rows=2449 width=1867) (actual time=0.031..0.099 rows=5 loops=1)

> Видно что он в ~2000 раз быстрее выполняется.
> Но чтобы использовался этот индекс приходится удалить один из двух других

А, скажите, вы статистику давно собирали? А то что-то в обоих случаях промах уж совсем большой получается.
И, кстати, с any запрос переписать не пробовали?

>>> Надо привести explain запроса, когда используется правильный индекс.
>> вот
>>> EXPLAIN ANALYZE SELECT
> ...
>> Index Scan using edispatcher_orders_service_idx on orders o (cost=
> 0.28..9592.12 rows=2449 width=1867) (actual time=0.031..0.099 rows=5 loops=1)

>> Видно что он в ~2000 раз быстрее выполняется.
>> Но чтобы использовался этот индекс приходится удалить один из двух других

> А, скажите, вы статистику давно собирали? А то что-то в обоих случаях промах уж
> совсем большой получается.

ANALYZE в смысле давно ли делал?
дык я эти индексы удалял и заново создавал.
и ANALYZE соответственно делал после каждой операции создания/удаления
индекса.

кроме того CREATE INDEX CONCURRENTLY насколько я знаю ANALYZE делает
сама, но впрочем я всегда явно делаю когда что-то в индексах меняю.

> И, кстати, с any запрос переписать не пробовали?

а там разницы нет
везде пишем status IN а Pg его переделывает в = ANY
переписывание запроса к = ANY не приводит ни к каким изменениям
EXPLAIN
--

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

> > А, скажите, вы статистику давно собирали? А то что-то в обоих случаях промах уж
> > совсем большой получается.
> ANALYZE в смысле давно ли делал?
> дык я эти индексы удалял и заново создавал.
> и ANALYZE соответственно делал после каждой операции создания/удаления
> индекса.

Но все равно интересно, почему и в том и в другом случае такой разброс между ожиданием и действительностью.

> > И, кстати, с any запрос переписать не пробовали?
> а там разницы нет

А с exists?
А на более свежей версии?

>>> А, скажите, вы статистику давно собирали? А то что-то в обоих случаях промах уж
>>> совсем большой получается.
>> ANALYZE в смысле давно ли делал?
>> дык я эти индексы удалял и заново создавал.
>> и ANALYZE соответственно делал после каждой операции создания/удаления
>> индекса.

> Но все равно интересно, почему и в том и в другом случае
> такой разброс между ожиданием и действительностью.

интересно вот еще что: когда этот запрос внедряли, то EXPLAIN смотрели
и он использовал нужный индекс.
затем прошло время, база подросла в размере где-то на 20% и Pg решил
больше этот индекс не использовать, а использовать два.

>>> И, кстати, с any запрос переписать не пробовали?
>> а там разницы нет

> А с exists?
> А на более свежей версии?

На более свежих еще нет.
у меня в плане стоит 9.5, но примерно как 9.5.1 выйдет я на нее начну
смотреть :)
--

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