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

SELECT
*
FROM
table
WHERE
("col1" IN (1,2,3) OR "col2" IN (3,4,5))
AND add_condition(blah)
;

таблица большая. в условии индекса add_condition(blah)

Если строить индекс по
col1, col2 WHERE add_condition(blah)

То получается перебор col2

если наоборот - то опять перебор col1

перебора не получается только если WITH/UNION секцию юзать и два индекса.
в два запроса:

SELECT
*
FROM
table
WHERE
col1 IN (1, 2, 3)
AND add_condition(blah)

и второй запрос

SELECT
*
FROM
table
WHERE
col2 IN (3,4,5)
AND col1 NOT IN (1,2,3)
AND add_condition(blah)

а можно как-то одним индесом сыграть?
--

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

Ну, можно построить GIN поверх созданного налету intarray, в котором
значения одного из столбцов придётся умножить на некое число (получив как
бы отдельное адресное прстранство), заведомо бОльшее, чем кардинальность
значений во втором столбце. И дальше использовать оператор <@, не забывая
умножать на то самое число все значения, которые предназначены для
сравнения с первым столбцом. Будет один индекс, но не факт что работающий
быстрее — важно понимать, какая кардинальность у обоих множеств, какая
селективность у запросов.

А главное, далеко не факт, опять же, что обновляться 2 btree будет
медленнее, чем такой GIN — то есть, если гонимся за скоростью модификаций,
можем и не выиграть.

Как по-другому сделать — непонятно, т.к. нужно, чтобы был 1 index entry для
одной строки. Если создавать один скаляр и использовать btree, то придётся
вводить новый оператор и писать контриб :).

Только зачем это всё? Посмотрите на статистику, что там с селективностью —
может, перебор по второму значению совершенно оправдан и стоит копейки?
Также странно, что запрос без LIMIT – опять же, всё упирается в
селективность, надо тщательно её оценить на будущее.
Если же дело принципа — делайте GIN.

2015-08-02 11:51 GMT+03:00 Dmitry E. Oboukhov <unera(at)debian(dot)org>:

> SELECT
> *
> FROM
> table
> WHERE
> ("col1" IN (1,2,3) OR "col2" IN (3,4,5))
> AND add_condition(blah)
> ;
>
> таблица большая. в условии индекса add_condition(blah)
>
> Если строить индекс по
> col1, col2 WHERE add_condition(blah)
>
> То получается перебор col2
>
> если наоборот - то опять перебор col1
>
> перебора не получается только если WITH/UNION секцию юзать и два индекса.
> в два запроса:
>
> SELECT
> *
> FROM
> table
> WHERE
> col1 IN (1, 2, 3)
> AND add_condition(blah)
>
>
> и второй запрос
>
> SELECT
> *
> FROM
> table
> WHERE
> col2 IN (3,4,5)
> AND col1 NOT IN (1,2,3)
> AND add_condition(blah)
>
>
> а можно как-то одним индесом сыграть?
> --
>
> . ''`. 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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (GNU/Linux)
>
> iEYEAREDAAYFAlW92fQACgkQq4wAz/jiZTfnBQCgl9t9qOT4zqoYHW9KhX3a4a1I
> Kd4AoJszfxhZo61yT+xTtZt4hBt0+XAe
> =xdGW
> -----END PGP SIGNATURE-----
>
>

Вообще говоря, для такого выражения мультиколоночный индекс работать не
будет, т.к. в OR оба аргумента равноправны. Вы правильно стали использовать
UNION (я так понимаю, UNION ALL, раз во втором выражении идёт фильрация NOT
IN), так что 2 индекса здесь — абсолютно нормально.

Если предполагается всё-таки вводить LIMIT, то быстрее такого UNION ALL
будет работать рекурсивный CTE, который «набирает» строчки из двух
«пространств», пока не наберёт достаточное количество. Здесь очень полезно
изучить приёмы Максима Богука
http://pgday.ru/files/pgmaster14/max.boguk.query.optimization.pdf.

2015-08-02 13:36 GMT+03:00 Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com>:

> Ну, можно построить GIN поверх созданного налету intarray, в котором
> значения одного из столбцов придётся умножить на некое число (получив как
> бы отдельное адресное прстранство), заведомо бОльшее, чем кардинальность
> значений во втором столбце. И дальше использовать оператор <@, не забывая
> умножать на то самое число все значения, которые предназначены для
> сравнения с первым столбцом. Будет один индекс, но не факт что работающий
> быстрее — важно понимать, какая кардинальность у обоих множеств, какая
> селективность у запросов.
>
> А главное, далеко не факт, опять же, что обновляться 2 btree будет
> медленнее, чем такой GIN — то есть, если гонимся за скоростью модификаций,
> можем и не выиграть.
>
> Как по-другому сделать — непонятно, т.к. нужно, чтобы был 1 index entry
> для одной строки. Если создавать один скаляр и использовать btree, то
> придётся вводить новый оператор и писать контриб :).
>
> Только зачем это всё? Посмотрите на статистику, что там с селективностью —
> может, перебор по второму значению совершенно оправдан и стоит копейки?
> Также странно, что запрос без LIMIT – опять же, всё упирается в
> селективность, надо тщательно её оценить на будущее.
> Если же дело принципа — делайте GIN.
>
> 2015-08-02 11:51 GMT+03:00 Dmitry E. Oboukhov <unera(at)debian(dot)org>:
>
>> SELECT
>> *
>> FROM
>> table
>> WHERE
>> ("col1" IN (1,2,3) OR "col2" IN (3,4,5))
>> AND add_condition(blah)
>> ;
>>
>> таблица большая. в условии индекса add_condition(blah)
>>
>> Если строить индекс по
>> col1, col2 WHERE add_condition(blah)
>>
>> То получается перебор col2
>>
>> если наоборот - то опять перебор col1
>>
>> перебора не получается только если WITH/UNION секцию юзать и два индекса.
>> в два запроса:
>>
>> SELECT
>> *
>> FROM
>> table
>> WHERE
>> col1 IN (1, 2, 3)
>> AND add_condition(blah)
>>
>>
>> и второй запрос
>>
>> SELECT
>> *
>> FROM
>> table
>> WHERE
>> col2 IN (3,4,5)
>> AND col1 NOT IN (1,2,3)
>> AND add_condition(blah)
>>
>>
>> а можно как-то одним индесом сыграть?
>> --
>>
>> . ''`. 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
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.10 (GNU/Linux)
>>
>> iEYEAREDAAYFAlW92fQACgkQq4wAz/jiZTfnBQCgl9t9qOT4zqoYHW9KhX3a4a1I
>> Kd4AoJszfxhZo61yT+xTtZt4hBt0+XAe
>> =xdGW
>> -----END PGP SIGNATURE-----
>>
>>
>