Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / не могу сделать индекс / 14 сообщений из 14, страница 1 из 1
03.10.2016, 12:36
    #39319542
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Доброго времени суток.
PostgreSQL 9.2.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

есть таблица
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
stat=#  select * from pg_stat_user_tables where relname= 'booking';


-[ RECORD 1 ]-----+------------------------------
relid             | 334669380
schemaname        | actualize
relname           | booking
seq_scan          | 316809
seq_tup_read      | 341408482385
idx_scan          | 3024718491
idx_tup_fetch     | 158872943466
n_tup_ins         | 1405051
n_tup_upd         | 34553636
n_tup_del         | 805479
n_tup_hot_upd     | 2671094
n_live_tup        | 2138094
n_dead_tup        | 295329
last_vacuum       | 2016-10-01 05:44:46.088902+00
last_autovacuum   | 2016-10-02 18:38:20.409949+00
last_analyze      |
last_autoanalyze  | 2016-10-02 18:47:01.768947+00
vacuum_count      | 1
autovacuum_count  | 36
analyze_count     | 0
autoanalyze_count | 54



Для запроса похожего на
Код: sql
1.
2.
3.
SELECT count(*) FROM actualize.booking
WHERE item_ids &&
(SELECT array_agg((random()* 100000)::int) FROM generate_series(1,3000))



план
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
Aggregate  (cost=445330.53..445330.54 rows=1 width=0) (actual time=1128346.740..1128346.741 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=20.00..20.02 rows=1 width=0) (actual time=2.460..2.460 rows=1 loops=1)
          ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=0) (actual time=0.543..0.970 rows=3000 loops=1)
  ->  Seq Scan on booking  (cost=0.00..445305.17 rows=2134 width=0) (actual time=77422.957..1128343.131 rows=2630 loops=1)
        Filter: (item_ids && $0)
        Rows Removed by Filter: 2128240
Total runtime: 1128346.926 ms



Почему так долго?
Для массива из 1000 план

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
Aggregate  (cost=445330.53..445330.54 rows=1 width=0) (actual time=308463.151..308463.152 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=20.00..20.02 rows=1 width=0) (actual time=0.413..0.413 rows=1 loops=1)
          ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=0) (actual time=0.098..0.184 rows=1000 loops=1)
  ->  Seq Scan on booking  (cost=0.00..445305.17 rows=2134 width=0) (actual time=46532.205..308462.083 rows=908 loops=1)
        Filter: (item_ids && $0)
        Rows Removed by Filter: 2130430
Total runtime: 308463.243 ms



Время уходит на фильтрацию?



Теперь основной вопрос
Пытаюсь сделать индекс
Код: sql
1.
2.
3.
4.
 CREATE INDEX CONCURRENTLY  inx_booking_item_ids
   ON actualize.booking
 USING gist  (item_ids gist__int_ops)
  ;



не закончился за 17 часов

Решил все прервать запустить монопольно.

Прервал после 1,5 часа.

Кто виноват и что делать? (с)

p.s. 9.6 такой запрос ускорит?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 12:58
    #39319569
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_,

а какую задачу решаете ?

индекс не может быть использован на не иммутабное значение -- пж обычно надо уметь узнать значение при составлении плана. это возможно для литерала или для имутабной ф-ии. и то не для всякой. например литерал date'today' сильно выгоднее "константы" current_date -- именно потому, что предвычисляется при составлении плана. (в плане будет торчать величина)

а результат агрегата -- именно не--иммутабен. Предвычислите его клиентом и передайте параметром -- может начать что--то использовать. Но вы ведь и индекс построить не можете . Какой длины у вас средний массив ? И зачем вам искать пересечение аж с 100000,, при ~ляме записей. Тут уж секскан будет всего вероятнее именно что дешевле. (порядка 1/10 ожидаемый возврат). очень может оказаться , что реляционная подчинёнка с нормальным индексом + какой--либо вариант самопального loose-индекскана у вас бы катил много шустрее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 13:45
    #39319615
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
qwwq,
Спасибо.

В приведенных примерах я не ищу 100000 записей.
В первом 3000 случайных записей, во втором 1000.

В реальной системе конечно же массив приходит реальный, а не случайный ).

Подавляющее большинство (99% ) длина массива 1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 14:46
    #39319670
p2.
p2.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_Почему так долго?2 млн строк на 3 тыс элементов массива = 6 млрд операций сравнения. За тысячу секунд не так уж и долго.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 15:21
    #39319704
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_,

а случайно intarray экстеншн не поставлен?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 15:32
    #39319716
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Alexius,

поставлен
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 15:37
    #39319722
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_,

ну вот виновник скорей всего. попобуйте со штатным оператором && запрос выполнить

Код: sql
1.
2.
3.
SELECT count(*) FROM actualize.booking
WHERE item_ids operator(pg_catalog.&&)
(SELECT array_agg((random()* 100000)::int) FROM generate_series(1,3000))



или спилите экстеншн, если не используется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 15:43
    #39319730
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_,

Отвечу на основной вопрос.
Читаю за вас докуметацию:
"
Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for small- to medium-size data sets, while gist__intbig_ops uses a larger signature and is more suitable for indexing large data sets (i.e., columns containing a large number of distinct array values). The implementation uses an RD-tree data structure with built-in lossy compression.
"
(и не создастся он по большой таблице без gist__intbig_ops за разумное время)

Ну и обязательно поставить большой maintenance_work_mem (4Gb например если памяти на сервере достаточно) на время создания индекса.


--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 16:00
    #39319758
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Alexius,
Спасибо.
Быстрее в разы.
Модуль используем. Может не стоит?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 16:02
    #39319764
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Maxim Boguk,

Спасибо.
В доке же указанно только для больших массивов.
"и не создастся он по большой таблице без gist__intbig_ops за разумное время)" это же Ваш опыт?
да и не думал я, что это очень большая таблица.

Еще раз спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 16:18
    #39319779
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_Alexius,
Спасибо.
Быстрее в разы.
Модуль используем. Может не стоит?

план покажите, да

ps мне , после накатки интеррея (в дефолтную схему) в тесте приходилось префиксы в динамику хранимок задним числом впихивать -- т.к. индекс был построен со стандартным оператором, а после перекрытия интерреевским -- не подхватывался. надо бы другой оператор использовать авторам (наш колхоз об таком не думает) -- в любом случае либо у одного либо у другого придётся префикс писать. маленькое неудобство, а спотыкаешься.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 16:41
    #39319805
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
С указанной Maxim Boguk опцией индекс создался.
Индекс используется для оператора из intarray.
Время поиска 1000 записей 120 сек

Для штатного оператора индекс не используется, что очевидно.

Время поиска 1000 записей 26 сек.



qwwq, план чего показать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2016, 17:00
    #39319817
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Gold_,

Сделал также gin-индекс.

Для штатного оператора с использованием gin-индекс.

Время поиска 1000 записей 300мс.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.10.2016, 15:03
    #39321098
Gold_
Gold_
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
не могу сделать индекс
Еще раз всем спасибо.
В результате ищу как сказал qwwq: "..реляционная подчинёнка с нормальным индексом.."
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / не могу сделать индекс / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]