Повышение эффективности запросов для повторяющихся запросов

Я пишу приложение node.js, чтобы включить поиск по базе данных PostgreSQL. Чтобы включить опережающее ввод текста в поле поиска Twitter, мне нужно перебрать набор ключевых слов из базы данных, чтобы инициализировать Bloodhound перед загрузкой страницы. Это что-то вроде ниже:

SELECT distinct handlerid from lotintro where char_length(lotid)=7;

Так что для большого стола (лотинтро) это дорого; это также глупо, поскольку результат запроса, скорее всего, остается одинаковым для разных посетителей в течение определенного периода времени.

Каков правильный способ справиться с этим? Думаю несколько вариантов:

1) Поместите запрос в хранимую процедуру и вызовите ее из node.js:

   SELECT * from getallhandlerid()

Означает ли это, что запрос будет скомпилирован, и база данных автоматически вернет те же наборы результатов без фактического запуска запроса, зная, что результат не изменится?

2) Или создать отдельную таблицу для хранения отдельных handlerid и обновлять таблицу с помощью триггера, который запускается каждый день? (Я знаю, что в идеале триггер должен запускаться для каждой вставки/обновления таблицы, но это слишком дорого).

3) создайте частичный индекс, как было предложено. Вот что собралось:

Запрос

SELECT distinct handlerid from lotintro where length(lotid) = 7;

Показатель

CREATE INDEX lotid7_idx ON lotintro (handlerid)
WHERE  length(lotid) = 7;

С индексом запрос стоит около 250 мс, попробуйте запустить

explain (analyze on, TIMING OFF) SELECT distinct handlerid from lotintro where length(lotid) = 7

"HashAggregate  (cost=5542.64..5542.65 rows=1 width=6) (actual rows=151 loops=1)"
"  ->  Bitmap Heap Scan on lotintro  (cost=39.08..5537.50 rows=2056 width=6) (actual rows=298350 loops=1)"
"        Recheck Cond: (length(lotid) = 7)"
"        Rows Removed by Index Recheck: 55285"
"        ->  Bitmap Index Scan on lotid7_idx  (cost=0.00..38.57 rows=2056 width=0) (actual rows=298350 loops=1)"
"Total runtime: 243.686 ms"

Без индекса запрос стоит около 210 мс, попробуйте запустить

explain (analyze on, TIMING OFF) SELECT distinct handlerid from lotintro where length(lotid) = 7

"HashAggregate  (cost=19490.11..19490.12 rows=1 width=6) (actual rows=151 loops=1)"
"  ->  Seq Scan on lotintro  (cost=0.00..19484.97 rows=2056 width=6) (actual rows=298350 loops=1)"
"        Filter: (length(lotid) = 7)"
"        Rows Removed by Filter: 112915"
"Total runtime: 214.235 ms"

Что я здесь делаю неправильно?

4) Использование предложенного Алексиусом индекса и запроса:

create index on lotintro using btree(char_length(lotid), handlerid);

Но это не оптимальное решение. Поскольку существует всего несколько различных значений, вы можете использовать трюк, называемый свободным сканированием индекса, который в вашем случае должен работать намного быстрее:

explain (analyze on, BUFFERS on, TIMING OFF)
WITH RECURSIVE t AS (
   (SELECT handlerid FROM lotintro WHERE char_length(lotid)=7 ORDER BY handlerid LIMIT 1)  -- parentheses required
   UNION ALL
   SELECT (SELECT handlerid FROM lotintro WHERE char_length(lotid)=7 AND handlerid > t.handlerid ORDER BY handlerid LIMIT 1)
   FROM t
   WHERE t.handlerid IS NOT NULL
   )
SELECT handlerid FROM t WHERE handlerid IS NOT NULL;

"CTE Scan on t  (cost=444.52..446.54 rows=100 width=32) (actual rows=151 loops=1)"
"  Filter: (handlerid IS NOT NULL)"
"  Rows Removed by Filter: 1"
"  Buffers: shared hit=608"
"  CTE t"
"    ->  Recursive Union  (cost=0.42..444.52 rows=101 width=32) (actual rows=152 loops=1)"
"          Buffers: shared hit=608"
"          ->  Limit  (cost=0.42..4.17 rows=1 width=6) (actual rows=1 loops=1)"
"                Buffers: shared hit=4"
"                ->  Index Scan using lotid_btree on lotintro lotintro_1  (cost=0.42..7704.41 rows=2056 width=6) (actual rows=1 loops=1)"
"                      Index Cond: (char_length(lotid) = 7)"
"                      Buffers: shared hit=4"
"          ->  WorkTable Scan on t t_1  (cost=0.00..43.83 rows=10 width=32) (actual rows=1 loops=152)"
"                Filter: (handlerid IS NOT NULL)"
"                Rows Removed by Filter: 0"
"                Buffers: shared hit=604"
"                SubPlan 1"
"                  ->  Limit  (cost=0.42..4.36 rows=1 width=6) (actual rows=1 loops=151)"
"                        Buffers: shared hit=604"
"                        ->  Index Scan using lotid_btree on lotintro  (cost=0.42..2698.13 rows=685 width=6) (actual rows=1 loops=151)"
"                              Index Cond: ((char_length(lotid) = 7) AND (handlerid > t_1.handlerid))"
"                              Buffers: shared hit=604"
"Planning time: 1.574 ms"
**"Execution time: 25.476 ms"**

========= больше информации о БД ===========================

dataloggerDB=# \d lotintro Таблица "public.lotintro"

    Column    |            Type             |  Modifiers
 --------------+-----------------------------+--------------
  lotstartdt   | timestamp without time zone | not null
  lotid        | text                        | not null
  ftc          | text                        | not null
  deviceid     | text                        | not null
  packageid    | text                        | not null
  testprogname | text                        | not null
  testprogdir  | text                        | not null
  testgrade    | text                        | not null
  testgroup    | text                        | not null
  temperature  | smallint                    | not null
  testerid     | text                        | not null
  handlerid    | text                        | not null
  numofsite    | text                        | not null
  masknum      | text                        |
  soaktime     | text                        |
  xamsqty      | smallint                    |
  scd          | text                        |
  speedgrade   | text                        |
  loginid      | text                        |
  operatorid   | text                        | not null
  loadboardid  | text                        | not null
  checksum     | text                        |
  lotenddt     | timestamp without time zone | not null
  totaltest    | integer                     | default (-1)
  totalpass    | integer                     | default (-1)
  earnhour     | real                        | default 0
  avetesttime  | real                        | default 0
  Indexes:
  "pkey_lotintro" PRIMARY KEY, btree (lotstartdt, testerid)
  "lotid7_idx" btree (handlerid) WHERE length(lotid) = 7

your version of Postgres,         [PostgreSQL 9.2]
cardinalities (how many rows?),   [411K rows for table lotintro]
percentage for length(lotid) = 7. [298350/411000=  73%]

============= после переноса всего на PG 9.4 ======================

С индексом:

explain (analyze on, BUFFERS on, TIMING OFF) SELECT distinct handlerid from lotintro where length(lotid) = 7

"HashAggregate  (cost=5542.78..5542.79 rows=1 width=6) (actual rows=151 loops=1)"
"  Group Key: handlerid"
"  Buffers: shared hit=14242"
"  ->  Bitmap Heap Scan on lotintro  (cost=39.22..5537.64 rows=2056 width=6) (actual rows=298350 loops=1)"
"        Recheck Cond: (length(lotid) = 7)"
"        Heap Blocks: exact=13313"
"        Buffers: shared hit=14242"
"        ->  Bitmap Index Scan on lotid7_idx  (cost=0.00..38.70 rows=2056 width=0) (actual rows=298350 loops=1)"
"              Buffers: shared hit=929"
"Planning time: 0.256 ms"
"Execution time: 154.657 ms"

Без индекса:

explain (analyze on, BUFFERS on, TIMING OFF) SELECT distinct handlerid from lotintro where length(lotid) = 7

"HashAggregate  (cost=19490.11..19490.12 rows=1 width=6) (actual rows=151 loops=1)"
"  Group Key: handlerid"
"  Buffers: shared hit=13316"
"  ->  Seq Scan on lotintro  (cost=0.00..19484.97 rows=2056 width=6) (actual rows=298350 loops=1)"
"        Filter: (length(lotid) = 7)"
"        Rows Removed by Filter: 112915"
"        Buffers: shared hit=13316"
"Planning time: 0.168 ms"
"Execution time: 176.466 ms"

postgresql stored-procedures triggers twitter-typeahead bloodhound
person sqr    schedule 09.05.2015    source источник
comment
автоматически возвращает те же наборы результатов без фактического выполнения запроса — нет. запрос будет выполняться каждый раз. 2) может быть достигнуто с помощью материализованного представления - вероятно, лучший подход   -  person a_horse_with_no_name    schedule 09.05.2015
comment
Спасибо! @a_horse_with_no_name   -  person sqr    schedule 09.05.2015
comment
Я только что нашел хорошую статью об этом. pgcon.org/2008/schedule/events/69.en.html< /а>   -  person sqr    schedule 09.05.2015
comment
стоимость запроса около 200 мс - стоимость запроса не указана в мс (на самом деле она вообще не имеет единиц измерения). Первый запрос с использованием индекса имеет стоимость 5542, второй (без использования индекса) имеет стоимость 19490 - в 3,5 раза выше - поэтому использование индекса более эффективно. Если вы хотите получить реальное время выполнения запроса (в мс), вам нужно использовать explain (analyze, timing).   -  person a_horse_with_no_name    schedule 10.05.2015
comment
@a_horse_with_no_name: чтобы получить наиболее точное сравнение производительности, EXPLAIN (ANALYZE, TIMING OFF) должно быть лучшим (только без учета внутренних деталей).   -  person Erwin Brandstetter    schedule 10.05.2015
comment
извините, мой плохой, 200 мс и 250 мс были средним значением того, что я видел в правом нижнем углу в инструменте запросов PostgreSQL при выполнении запроса. Это фактическая продолжительность выполнения запроса?   -  person sqr    schedule 10.05.2015
comment
обновленный тикет с подробностями «объяснить анализ». Благодарю.   -  person sqr    schedule 10.05.2015
comment
Пожалуйста, больше информации: определение таблицы (\d lotintro в psql), ваша версия Postgres, кардинальность (сколько строк?), процент для length(lotid) = 7. В целях оптимизации вывод EXPLAIN (BUFFERS, ANALYZE) более полезен. Что произойдет, если вы повторите тест после ANALYZE, затем после VACUUM ANALYZE, затем (только если вы можете позволить себе эксклюзивную блокировку!) после VACUUM FULL ANALYZE. Скорее всего, вы увидите сканирование только индекса после VACUUM FULL, которое значительно быстрее (но эффект ухудшается со временем, если у вас много записей в таблицу).   -  person Erwin Brandstetter    schedule 11.05.2015
comment
привет @Erwin Brandstetter, я только что обновил детали в вопросах. Да, кажется, что вакуум может сыграть здесь значительную роль; когда я перенес данные с PG9.2 на PG9.4, производительность изменилась на противоположную. Индексированный запрос работает лучше.   -  person sqr    schedule 11.05.2015

Ответы (3)


arrow_upward
0
arrow_downward

1)

Нет, функция никоим образом не сохраняет моментальные снимки результата. Существует некоторый потенциал для оптимизации производительности, если вы определите функцию STABLE (что было бы правильно). Согласно документации:

Функция STABLE не может изменять базу данных и гарантированно возвращает одни и те же результаты при одинаковых аргументах для всех строк в одном операторе.

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

Таким образом, это может огромно принести пользу нескольким вызовам в одном и том же выражении, но это не соответствует вашему варианту использования...

А функции plpgsql работают как подготовленные операторы, предоставляя аналогичный бонус внутри одного и того же сеанса:

2)

Попробуйте MATERIALIZED VIEW. С MV или без него (или каким-либо другим методом кэширования), частичный индекс будет наиболее эффективным для вашего особого случая:

CREATE INDEX lotid7_idx ON lotintro (handlerid)
WHERE  length(lotid) = 7;

Не забудьте включить условие индекса в запросы, которые должны использовать индекс, даже если это кажется излишним:

Однако, как вы указали:

процент длины (lotid) = 7. [298350/411000= 73%]

Этот индекс поможет только в том случае, если вы сможете получить из него сканирование только по индексу, потому что условие вряд ли является избирательным. Поскольку в таблице очень широкие строки, сканирование только по индексу может быть значительно быстрее.

Свободное сканирование индекса

Кроме того, rows=298350 сворачиваются в rows=151, поэтому свободное сканирование индекса окупится, как я объяснял здесь:

Или в Вики Postgres, которая фактически основана на этом сообщении.

WITH RECURSIVE t AS (
   (SELECT handlerid FROM lotintro
    WHERE  length(lotid) = 7
    ORDER  BY 1 LIMIT 1)

   UNION ALL
   SELECT (SELECT handlerid FROM lotintro
           WHERE  length(lotid) = 7
           AND    handlerid > t.handlerid
           ORDER  BY 1 LIMIT 1)
   FROM  t
   WHERE t.handlerid IS NOT NULL
   )
SELECT handlerid FROM t
WHERE  handlerid IS NOT NULL;

Это будет быстрее, но я предложил в сочетании с частичным индексом. Поскольку частичный индекс вдвое меньше и обновляется реже (зависит от шаблонов доступа), в целом он дешевле.

Еще быстрее, если вы оставите таблицу очищенной, чтобы разрешить сканирование только индекса. Вы можете установить более агрессивные параметры хранения только для этой таблицы, если у вас много записей в нее:

Наконец, вы можете сделать это еще быстрее с материализованным представлением, основанным на этом запросе.

person Erwin Brandstetter    schedule 09.05.2015
comment
Привет, @Erwin Brandstetter, я пробую частичный индекс здесь. Похоже, что после добавления индекса становится хуже. Я попытался запустить «EXPLAIN», и он подтвердил, что запрос действительно использует индекс. Что здесь может пойти не так? -- Я обновил детали в постановке вопроса. Спасибо! - person sqr; 10.05.2015
comment
@sqr: Можешь повторить тест с EXPLAIN (ANALYZE, TIMING OFF)? (Лучший из 5.) - person Erwin Brandstetter; 10.05.2015
comment
несколько сторон редактируют в нескольких местах этого билета, и в итоге я увидел ответ Алекса до того, как вы отредактировали «свободное сканирование индекса». Как я вижу сейчас, в вашем ответе были предложены все возможные решения с полными объяснениями, и я отмечу ваш ответ как ответ. Спасибо. -- Я думал, что смогу отметить несколько ответов раньше. - person sqr; 11.05.2015

arrow_upward
1
arrow_downward

Вам нужно проиндексировать точное выражение, которое используется в вашем предложении WHERE: http://www.postgresql.org/docs/9.4/static/indexes-expressional.html

CREATE INDEX char_length_lotid_idx ON lotintro (char_length(lotid));

Вы также можете создать функцию STABLE или IMMUTABLE, чтобы обернуть этот запрос, как вы предложили: http://www.postgresql.org/docs/9.4/static/sql-createfunction.html

Ваше последнее предложение также жизнеспособно, вы ищете MATERIALIZED VIEWS: http://www.postgresql.org/docs/9.4/static/sql-creatematerializedview.html Это не позволит вам написать собственный триггер для обновления денормализованной таблицы.

person Clément Prévost    schedule 09.05.2015
comment
Пожалуйста :). Мне интересно, какое решение лучше всего подходит для вас и с каким объемом данных вы имеете дело. Как долго длится запрос сейчас? а с индексом? - person Clément Prévost; 09.05.2015
comment
Привет @ Clément Prévost, в таблице lotintro 411 тыс. строк, а отдельный handlerid — около 150 строк. Простой SQL-запрос или хранимая процедура занимают около 220 мс. Первоначально я использовал PostgreSQL 9.2, который не поддерживает MV. Теперь я перенес ту же таблицу в PostgreSQL 9.4, и да, MV работает, как и ожидалось, и это почти мгновенно (‹1 мс). - person sqr; 11.05.2015

arrow_upward
1
arrow_downward

Поскольку 3/4 строк удовлетворяют вашему условию (длина (lotid) = 7), сам индекс мало чем поможет. Вы можете получить немного лучшую производительность с этим индексом из-за сканирования только индекса:

create index on lotintro using btree(char_length(lotid), handlerid);

Но это не оптимальное решение. Поскольку различных значений всего несколько, вы можете использовать прием под названием сканирование свободного индекса, который должен работать намного быстрее. в твоем случае:

WITH RECURSIVE t AS (
   (SELECT handlerid FROM lotintro WHERE char_length(lotid)=7 ORDER BY handlerid LIMIT 1)  -- parentheses required
   UNION ALL
   SELECT (SELECT handlerid FROM lotintro WHERE char_length(lotid)=7 AND handlerid > t.handlerid ORDER BY handlerid LIMIT 1)
   FROM t
   WHERE t.handlerid IS NOT NULL
   )
SELECT handlerid FROM t WHERE handlerid IS NOT NULL;

для этого запроса вам также необходимо создать индекс, о котором я упоминал выше.

person alexius    schedule 11.05.2015
comment
Привет @alexius, это работает намного лучше, хотя мне нужно еще немного времени, чтобы понять, что вы здесь пытаетесь. Я также добавлю ваш ответ в формулировку вопроса. Спасибо! - person sqr; 11.05.2015