Пример чтения плана запроса

Пример чтения плана запроса#

В этом разделе показано, как читать план выполнения SQL-запроса в PostgreSQL. На примере разбирается, откуда начинается чтение строк, в каком порядке выполняются соединения и как интерпретировать вложенные подзапросы.

Запрос#

SELECT t.id
FROM Wf_Dir t
JOIN Wf_DirType dt ON dt.idObjectType = t.idObjectType
WHERE t.idParent IS NULL
    AND (
        coalesce(dt.bForFolderAccess, 0) = 1
        AND t.idObjectType != 498
        OR EXISTS (
            SELECT rights.id
            FROM Btk_RlsUserRightsFlat rights
            WHERE rights.gidobj = t.gid
                AND (
                    (rights.bCanView = 1 AND rights.idUser = 20562)
                    OR (
                        t.idObjectType = 498
                        AND FALSE
                        AND coalesce(0, 0) = 1
                    )
                )
        )
        OR EXISTS (
            SELECT d.idParent
            FROM Wf_DirLinkDenorm d
            LEFT JOIN Wf_Dir w ON d.idParent = w.id
            LEFT JOIN Btk_RlsUserRightsFlat r ON r.gidobj = w.gid
            WHERE d.idInheritor = t.id
                AND r.idUser = 20562
                AND r.bCanView = 1
        )
    );

План выполнения#

Nested Loop
  Output: t.id
  ->  Index Scan using idx_wf_dir_idparent on public.wf_dir t
        Output: t.id, t.idclass, t.gid, t.scode, t.scaption, t.nimage, t.idparent, t.idobjecttype, t.iddocobjecttype, t.bforbidcreatefileincurrentdir, t.sdescription, t.bforbidchangedoctypebydir, t.bdeleted, t.gidsrc, t.buniquedesignationindir, t.buniquedesignationinsubdir, t.iddirtemplate, t.idowneruser, t.screateuser_dz, t.sbopath_dz, t.jidext_dz, t.smodifyuser_dz, t.jsig_dz, t.jsign_dz, t.jsysdata_dz, t.jobjattrs_dz, t.smnemocode_dz, t.idlockunit_dz, t.sheadline_dz, t.sidext_dz, t.dmodifydate_dz, t.gidroot_dz, t.dcreatedate_dz, t.nversion_dz, t.idbisobj, t.iddepowner, t.gidsrcreverse, t.jtextlang_dz, t.ddeletecollection_dz, t.sdeletecollectionuser_dz
        Index Cond: (t.idparent IS NULL)
        Filter: ((t.idobjecttype <> $3) OR EXISTS(SubPlan 1) OR (ANY (t.id = (hashed SubPlan 4).col1)))
        SubPlan 1
          ->  Index Scan using idx_btk_rlsuserrightsflat_gidobj on public.btk_rlsuserrightsflat rights
                Index Cond: (((rights.gidobj)::text = (t.gid)::text) AND (rights.iduser = $5))
                Filter: (rights.bcanview = $4)
        SubPlan 4
          ->  Gather
                Output: d_1.idinheritor
                Workers Planned: 1
                ->  Merge Join
                      Output: d_1.idinheritor
                      Merge Cond: (w_1.id = d_1.idparent)
                      ->  Nested Loop
                            Output: w_1.id
                            ->  Parallel Index Scan using wf_dir_pkey on public.wf_dir w_1
                                  Output: w_1.id, w_1.idclass, w_1.gid, w_1.scode, w_1.scaption, w_1.nimage, w_1.idparent, w_1.idobjecttype, w_1.iddocobjecttype, w_1.bforbidcreatefileincurrentdir, w_1.sdescription, w_1.bforbidchangedoctypebydir, w_1.bdeleted, w_1.gidsrc, w_1.buniquedesignationindir, w_1.buniquedesignationinsubdir, w_1.iddirtemplate, w_1.idowneruser, w_1.screateuser_dz, w_1.sbopath_dz, w_1.jidext_dz, w_1.smodifyuser_dz, w_1.jsig_dz, w_1.jsign_dz, w_1.jsysdata_dz, w_1.jobjattrs_dz, w_1.smnemocode_dz, w_1.idlockunit_dz, w_1.sheadline_dz, w_1.sidext_dz, w_1.dmodifydate_dz, w_1.gidroot_dz, w_1.dcreatedate_dz, w_1.nversion_dz, w_1.idbisobj, w_1.iddepowner, w_1.gidsrcreverse, w_1.jtextlang_dz, w_1.ddeletecollection_dz, w_1.sdeletecollectionuser_dz
                            ->  Index Scan using idx_btk_rlsuserrightsflat_gidobj on public.btk_rlsuserrightsflat r_1
                                  Output: r_1.id, r_1.idclass, r_1.gid, r_1.gidobj, r_1.iduser, r_1.idaccessgroup, r_1.bcanedit, r_1.bcanview, r_1.bcandelete, r_1.bcanadmin, r_1.iduserfrom, r_1.idrolefrom, r_1.gidsrc, r_1.screateuser_dz, r_1.sbopath_dz, r_1.jidext_dz, r_1.smodifyuser_dz, r_1.jsig_dz, r_1.jsign_dz, r_1.jsysdata_dz, r_1.jobjattrs_dz, r_1.smnemocode_dz, r_1.sheadline_dz, r_1.sidext_dz, r_1.dmodifydate_dz, r_1.gidroot_dz, r_1.dcreatedate_dz, r_1.nversion_dz, r_1.jtextlang_dz, r_1.ddeletecollection_dz, r_1.sdeletecollectionuser_dz
                                  Index Cond: (((r_1.gidobj)::text = (w_1.gid)::text) AND (r_1.iduser = $11))
                                  Filter: (r_1.bcanview = $12)
                      ->  Index Scan using idx_wf_dirlinkdenorm_idparent on public.wf_dirlinkdenorm d_1
                            Output: d_1.id, d_1.idclass, d_1.idparent, d_1.idinheritor, d_1.bismain, d_1.screateuser_dz, d_1.sbopath_dz, d_1.jidext_dz, d_1.smodifyuser_dz, d_1.jsig_dz, d_1.jsign_dz, d_1.jsysdata_dz, d_1.jobjattrs_dz, d_1.smnemocode_dz, d_1.sheadline_dz, d_1.sidext_dz, d_1.dmodifydate_dz, d_1.gidroot_dz, d_1.dcreatedate_dz, d_1.nversion_dz, d_1.arraydirlink, d_1.jtextlang_dz, d_1.ddeletecollection_dz, d_1.sdeletecollectionuser_dz
  ->  Index Scan using idx_wf_dirtype_idobjecttype on public.wf_dirtype dt
        Output: dt.id, dt.idclass, dt.idobjecttype, dt.nimage, dt.scontentsel, dt.scontentrep, dt.bforbidmanualsetot, dt.screateuser_dz, dt.sbopath_dz, dt.jidext_dz, dt.smodifyuser_dz, dt.jsig_dz, dt.jsign_dz, dt.jsysdata_dz, dt.jobjattrs_dz, dt.smnemocode_dz, dt.sheadline_dz, dt.sidext_dz, dt.dmodifydate_dz, dt.gidroot_dz, dt.dcreatedate_dz, dt.nversion_dz, dt.bforfolderaccess, dt.jtextlang_dz, dt.ddeletecollection_dz, dt.sdeletecollectionuser_dz
        Index Cond: (dt.idobjecttype = t.idobjecttype)
        Filter: (((COALESCE(dt.bforfolderaccess, $1) = $2) AND (t.idobjecttype <> $3)) OR EXISTS(SubPlan 1) OR EXISTS(SubPlan 3))
        SubPlan 3
          ->  Nested Loop
                ->  Nested Loop
                      Output: w.gid
                      Inner Unique: true
                      ->  Index Scan using idx_wf_dirlinkdenorm_idinheritor on public.wf_dirlinkdenorm d
                            Output: d.id, d.idclass, d.idparent, d.idinheritor, d.bismain, d.screateuser_dz, d.sbopath_dz, d.jidext_dz, d.smodifyuser_dz, d.jsig_dz, d.jsign_dz, d.jsysdata_dz, d.jobjattrs_dz, d.smnemocode_dz, d.sheadline_dz, d.sidext_dz, d.dmodifydate_dz, d.gidroot_dz, d.dcreatedate_dz, d.nversion_dz, d.arraydirlink, d.jtextlang_dz, d.ddeletecollection_dz, d.sdeletecollectionuser_dz
                            Index Cond: (d.idinheritor = t.id)
                      ->  Index Scan using wf_dir_pkey on public.wf_dir w
                            Output: w.id, w.idclass, w.gid, w.scode, w.scaption, w.nimage, w.idparent, w.idobjecttype, w.iddocobjecttype, w.bforbidcreatefileincurrentdir, w.sdescription, w.bforbidchangedoctypebydir, w.bdeleted, w.gidsrc, w.buniquedesignationindir, w.buniquedesignationinsubdir, w.iddirtemplate, w.idowneruser, w.screateuser_dz, w.sbopath_dz, w.jidext_dz, w.smodifyuser_dz, w.jsig_dz, w.jsign_dz, w.jsysdata_dz, w.jobjattrs_dz, w.smnemocode_dz, w.idlockunit_dz, w.sheadline_dz, w.sidext_dz, w.dmodifydate_dz, w.gidroot_dz, w.dcreatedate_dz, w.nversion_dz, w.idbisobj, w.iddepowner, w.gidsrcreverse, w.jtextlang_dz, w.ddeletecollection_dz, w.sdeletecollectionuser_dz
                            Index Cond: (w.id = d.idparent)
                ->  Index Scan using idx_btk_rlsuserrightsflat_gidobj on public.btk_rlsuserrightsflat r
                      Output: r.id, r.idclass, r.gid, r.gidobj, r.iduser, r.idaccessgroup, r.bcanedit, r.bcanview, r.bcandelete, r.bcanadmin, r.iduserfrom, r.idrolefrom, r.gidsrc, r.screateuser_dz, r.sbopath_dz, r.jidext_dz, r.smodifyuser_dz, r.jsig_dz, r.jsign_dz, r.jsysdata_dz, r.jobjattrs_dz, r.smnemocode_dz, r.sheadline_dz, r.sidext_dz, r.dmodifydate_dz, r.gidroot_dz, r.dcreatedate_dz, r.nversion_dz, r.jtextlang_dz, r.ddeletecollection_dz, r.sdeletecollectionuser_dz
                      Index Cond: (((r.gidobj)::text = (w.gid)::text) AND (r.iduser = $11))
                      Filter: (r.bcanview = $12)

Как читать план выполнения#

План выполнения представляет собой дерево операций. При чтении плана важно различать:

  • структуру плана;

  • порядок получения строк внутри узлов.

Структуру плана читают сверху вниз. Порядок получения строк зависит от типа узла и его дочерних веток.

Например, для узла такого вида:

Nested Loop
  -> A
  -> B

порядок работы будет следующим:

  1. PostgreSQL получает строки из узла A.

  2. Для каждой строки из A выполняется узел B.

В Nested Loop левая ветка является внешним источником строк, а правая — внутренней операцией, которая выполняется для каждой строки внешней ветки.

С чего начинается выполнение#

В рассматриваемом примере стартовым источником строк является узел:

->  Index Scan using idx_wf_dir_idparent on public.wf_dir t
      Index Cond: (t.idparent IS NULL)

Это означает, что сначала PostgreSQL обращается к таблице wf_dir по индексу idx_wf_dir_idparent и выбирает строки, которые удовлетворяют условию:

t.idParent IS NULL

С этого набора строк начинается выполнение основного плана.

Как определить порядок соединения#

Верхний узел плана имеет вид:

Nested Loop
  -> Index Scan ... on wf_dir t
  -> Index Scan ... on wf_dirtype dt

Из этого следует такой порядок:

  1. Из wf_dir t выбирается очередная строка.

  2. По значению t.idobjecttype в wf_dirtype dt ищется соответствующая строка.

  3. После этого применяются дополнительные фильтры.

Иными словами, соединение

JOIN Wf_DirType dt ON dt.idObjectType = t.idObjectType

выполняется как Nested Loop, где wf_dir является внешней таблицей, а wf_dirtype — внутренней.

Как читать Index Cond и Filter#

При разборе плана важно различать два вида условий.

Index Cond#

Index Cond показывает условие, которое PostgreSQL использует для доступа по индексу.

Пример:

Index Cond: (dt.idobjecttype = t.idobjecttype)

Это означает, что строка в wf_dirtype ищется по индексу сразу по ключу соединения.

Filter#

Filter показывает условие, которое проверяется после того, как строка-кандидат уже получена.

Пример:

Filter: ((t.idobjecttype <> $3) OR EXISTS(SubPlan 1) OR (ANY (t.id = (hashed SubPlan 4).col1)))

Это означает, что строка t, найденная через индекс по idparent, дополнительно проверяется по логическому условию. Не все условия запроса участвуют в индексном доступе. Часть условий применяется только как постфильтр.

Практически это означает следующее:

  • Index Cond определяет способ поиска строк;

  • Filter определяет отбор уже найденных строк.

Как читать SubPlan#

SubPlan — это отдельный вложенный фрагмент плана. Обычно он соответствует конструкции EXISTS, IN или другому подзапросу.

В этом примере подзапросы используются для проверки прав доступа. Поэтому основная сложность плана связана не с соединением wf_dir и wf_dirtype, а с дополнительными условиями доступа.

Разбор SubPlan 1#

Фрагмент:

SubPlan 1
  ->  Index Scan using idx_btk_rlsuserrightsflat_gidobj on public.btk_rlsuserrightsflat rights
        Index Cond: (((rights.gidobj)::text = (t.gid)::text) AND (rights.iduser = $5))
        Filter: (rights.bcanview = $4)

Этот подзапрос соответствует проверке прямого права доступа к текущему объекту t. Он использует t.gid, то есть зависит от текущей строки внешнего запроса. Такой подзапрос является коррелированным.

Порядок проверки следующий:

  1. Для текущей строки t в таблице Btk_RlsUserRightsFlat ищутся записи по gidobj и iduser.

  2. Для найденных записей проверяется bcanview = 1.

  3. Если подходящая запись найдена, условие EXISTS считается истинным.

Разбор hashed SubPlan 4#

Фрагмент:

ANY (t.id = (hashed SubPlan 4).col1)

означает, что PostgreSQL:

  1. Сначала выполняет SubPlan 4;

  2. По его результату строит хеш-таблицу;

  3. Затем для каждой строки t проверяет, входит ли t.id в рассчитанное множество.

По смыслу это эквивалентно проверке принадлежности t.id набору значений, вычисленному подзапросом.

Внутренняя структура SubPlan 4#

SubPlan 4
  -> Gather
       -> Merge Join
            Merge Cond: (w_1.id = d_1.idparent)
            -> Nested Loop
                 -> Parallel Index Scan on wf_dir w_1
                 -> Index Scan on btk_rlsuserrightsflat r_1
            -> Index Scan on wf_dirlinkdenorm d_1

Порядок чтения здесь следующий:

  1. Формируется левая ветка Merge Join:

    • PostgreSQL читает строки из wf_dir w_1;

    • для каждой строки w_1 ищет соответствующие права в btk_rlsuserrightsflat r_1;

    • оставляет только строки, на которые у пользователя есть право просмотра.

  2. Отдельно читается правая ветка wf_dirlinkdenorm d_1.

  3. Затем выполняется Merge Join по условию:

    w_1.id = d_1.idparent

  4. В результат попадают значения d_1.idinheritor.

  5. Над результатом выполняется Gather, так как часть работы распараллелена.

Практический смысл SubPlan 4: заранее вычисляется множество наследников каталогов, доступных пользователю через права на родительские узлы.

Разбор SubPlan 3#

Фрагмент:

SubPlan 3
  ->  Nested Loop
        ->  Nested Loop
              ->  Index Scan using idx_wf_dirlinkdenorm_idinheritor on public.wf_dirlinkdenorm d
                    Index Cond: (d.idinheritor = t.id)
              ->  Index Scan using wf_dir_pkey on public.wf_dir w
                    Index Cond: (w.id = d.idparent)
        ->  Index Scan using idx_btk_rlsuserrightsflat_gidobj on public.btk_rlsuserrightsflat r
              Index Cond: (((r.gidobj)::text = (w.gid)::text) AND (r.iduser = $11))
              Filter: (r.bcanview = $12)

Этот подзапрос проверяет доступ к текущему объекту t через родительский каталог.

Порядок выполнения следующий:

  1. По условию d.idinheritor = t.id в wf_dirlinkdenorm находятся связи наследования.

  2. По d.idparent в wf_dir находится родительский каталог w.

  3. По w.gid в Btk_RlsUserRightsFlat ищутся права пользователя.

  4. Дополнительно проверяется признак bcanview = 1.

По сути, здесь проверяется, есть ли у пользователя доступ к одному из родительских узлов текущего объекта.

Итоговый порядок выполнения#

Если не учитывать детали внутренних подзапросов, основной порядок работы выглядит так:

  1. PostgreSQL выбирает корневые записи из wf_dir по условию t.idparent IS NULL.

  2. Для каждой строки t применяется фильтр верхнего узла:

    • либо t.idobjecttype <> 498;

    • либо выполняется SubPlan 1;

    • либо t.id входит в результат hashed SubPlan 4.

  3. Для строки t, прошедшей проверку, находится запись в wf_dirtype по условию dt.idobjecttype = t.idobjecttype.

  4. Для найденной строки dt применяется второй фильтр:

    • либо coalesce(dt.bforfolderaccess, 0) = 1 и t.idobjecttype <> 498;

    • либо выполняется SubPlan 1;

    • либо выполняется SubPlan 3.

  5. Если строка проходит все проверки, в результат попадает t.id.

На что обращать внимание при чтении таких планов#

При разборе подобных запросов рекомендуется придерживаться следующего порядка:

  1. Определить стартовый источник строк.

  2. Восстановить основной порядок соединения таблиц.

  3. Отделить условия индексного доступа от постфильтрации.

  4. Найти все SubPlan.

  5. Проверить, какие из них коррелированы с внешними строками.

  6. Только после этого переходить к анализу cost, actual rows, loops и buffers.

Содержание