Вложенное левое соединение цикла стоит слишком много времени?

Это query:

EXPLAIN (analyze, BUFFERS, SETTINGS)
SELECT
    operation.id
FROM
    operation
RIGHT JOIN(
    SELECT uid, did FROM (
            SELECT uid, did FROM operation where id = 993754
        ) t
    ) parts ON (operation.uid = parts.uid AND operation.did = parts.did)

и EXPLAIN информация:

Nested Loop Left Join  (cost=0.85..29695.77 rows=100 width=8) (actual time=13.709..13.711 rows=1 loops=1)
  Buffers: shared hit=4905
  ->  Unique  (cost=0.42..8.45 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=1)
        Buffers: shared hit=5
        ->  Index Only Scan using oi on operation operation_1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
              Index Cond: (id = 993754)
              Heap Fetches: 1
              Buffers: shared hit=5
  ->  Index Only Scan using oi on operation  (cost=0.42..29686.32 rows=100 width=24) (actual time=13.695..13.696 rows=1 loops=1)
        Index Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
        Heap Fetches: 1
        Buffers: shared hit=4900
Settings: max_parallel_workers_per_gather = '4', min_parallel_index_scan_size = '0', min_parallel_table_scan_size = '0', parallel_setup_cost = '0', parallel_tuple_cost = '0', work_mem = '256MB'
Planning Time: 0.084 ms
Execution Time: 13.728 ms

Почему Nested Loop стоит все больше и больше времени, чем сумма дочерних элементов? Что я могу для этого сделать? Execution Time должно быть меньше 1 мс, верно?

Обновить:

Nested Loop Left Join  (cost=5.88..400.63 rows=101 width=8) (actual time=0.012..0.012 rows=1 loops=1)
  Buffers: shared hit=8
  ->  Index Scan using oi on operation operation_1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1)
        Index Cond: (id = 993754)
        Buffers: shared hit=4
  ->  Bitmap Heap Scan on operation  (cost=5.45..391.19 rows=100 width=24) (actual time=0.004..0.005 rows=1 loops=1)
        Recheck Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
        Heap Blocks: exact=1
        Buffers: shared hit=4
        ->  Bitmap Index Scan on ou  (cost=0.00..5.42 rows=100 width=0) (actual time=0.003..0.003 rows=1 loops=1)
              Index Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
              Buffers: shared hit=3
Settings: max_parallel_workers_per_gather = '4', min_parallel_index_scan_size = '0', min_parallel_table_scan_size = '0', parallel_setup_cost = '0', parallel_tuple_cost = '0', work_mem = '256MB'
Planning Time: 0.127 ms
Execution Time: 0.028 ms

Спасибо всем вам, когда я разделил индекс на btree(id) и btree(uid, did), все прошло идеально, но почему их нельзя использовать вместе? Какие-то подробности или правила?

Кстати, sql используется для расчета в реальном времени, здесь не показан код некоторых оконных функций.


postgresql performance explain
person simline    schedule 14.12.2019    source источник
comment
Что происходит при повторном выполнении? Кроме того, можете ли вы включить track_io_timing?   -  person jjanes    schedule 14.12.2019
comment
Основное время уходит на сканирование только индексов. Вложенный цикл добавляет к этому всего около 0,015 мс. Общее время в каждом узле включает также всех дочерних элементов.   -  person a_horse_with_no_name    schedule 14.12.2019
comment
Порядок столбцов в многостолбцовом индексе важен. Индекс можно эффективно использовать только в том случае, если существуют ограничения для крайних левых столбцов. Также обновил мой ответ. Рассмотрите возможность создания нового вопроса в будущем для вашего обновления, потому что на ваш исходный вопрос был дан ответ, и он содержит другой вопрос.   -  person Florian Gutmann    schedule 15.12.2019

Ответы (2)


arrow_upward
4
arrow_downward

На самом деле Nested Loop не занимает много времени. Фактическое время 13.709..13.711 означает, что потребовалось 13.709 мс, прежде чем первая строка была готова к отправке из этого узла, и потребовалось 0.002 мс, пока она не была завершена.

Обратите внимание, что стоимость запуска 13.709 мс включает стоимость двух его дочерних узлов. Оба дочерних узла должны создать хотя бы одну строку, прежде чем вложенный цикл сможет начаться.

Дочерний элемент Unique начал выдавать свою первую (и единственную) строку через 0.011 мс. Однако дочерний элемент Index Only Scan начал выдавать свою первую (и единственную) строку только через 13.695 мс. Это означает, что большую часть вашего фактического времени вы проводите в этом Index Only Scan.

Здесь есть отличный ответ , который объясняет затраты и фактическое время в глубину.

Также есть хороший инструмент на https://explain.depesz.com, который рассчитывает инклюзивное и эксклюзивное время для каждый узел. Здесь он используется для вашего плана запроса, который ясно показывает, что большая часть времени тратится на Index Only Scan.

Поскольку запрос тратит почти все время на сканирование только этого индекса, оптимизация в нем принесет наибольшую пользу. Создание отдельного индекса для столбцов uid и did в таблице operation должно значительно сократить время запроса.

CREATE INDEX operation_uid_did ON operation(uid, did);

Текущий план выполнения содержит 2 сканирования только индекса.

Медленный:

  ->  Index Only Scan using oi on operation  (cost=0.42..29686.32 rows=100 width=24) (actual time=13.695..13.696 rows=1 loops=1)
        Index Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
        Heap Fetches: 1
        Buffers: shared hit=4900

И быстрый:

  ->  Index Only Scan using oi on operation operation_1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
        Index Cond: (id = 993754)
        Heap Fetches: 1
        Buffers: shared hit=5

Оба они используют индекс oi, но имеют разные условия индекса. Обратите внимание, что быстрому пользователю, использующему условие индекса id, нужно загрузить только 5 страниц данных (Buffers: shared hit=5). Вместо этого медленному нужно загрузить 4900 страниц (Buffers: shared hit=4900). Это указывает на то, что индекс оптимизирован для запроса id, но не столько для uid и did. Вероятно, индекс oi охватывает все 3 столбца id, uid, did в этом порядке.

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

person Florian Gutmann    schedule 14.12.2019
comment
Я понял, спасибо. Почитаю официальную документацию внимательнее. - person simline; 16.12.2019

arrow_upward
1
arrow_downward

Почему вложенный цикл стоит все больше и больше времени, чем сумма дочерних элементов?

Судя по вашему примеру, это не так. Можете ли вы уточнить, что заставляет вас думать, что это так?

В любом случае, посещение 4900 страниц для получения 1 кортежа кажется экстравагантным. Я предполагаю, что ваши столы недостаточно пропылесосены.

Хотя теперь я предпочитаю предложение Флориана о том, что «uid» и «did» не являются ведущими столбцами индекса, и поэтому он медленный. По сути, он выполняет полное сканирование индекса, используя индекс как укороченную версию таблицы. Жаль, что вывод EXPLAIN не дает понять, когда индекс используется таким образом, а не традиционный «переход к определенной части индекса».

Итак, у вас отсутствует индекс.

person jjanes    schedule 14.12.2019
comment
4900 страниц действительно избыточны. Индекс oi используется в плане дважды. Один раз с условием id и один раз uid и did. Поскольку id доступ является разумным, я пришел к выводу, что индекс покрывает что-то вроде (id, uid, did) и поэтому работает довольно медленно только на uid и did. Считаете ли вы этот вывод разумным? - person Florian Gutmann; 14.12.2019
comment
Да, это кажется разумным. - person jjanes; 14.12.2019