Início / dba / Perguntas / 334310
Accepted
George K
Asked: 2023-12-24 03:17:45 +0800 CST

Pergunta complexa de otimização de consulta PostgreSQL

  • 772

Por favor, ajude com a consulta. Os desenvolvedores vieram pedindo para melhorar o desempenho da consulta. Tentei usar CTEs em vez de junções laterais e EXISTS, testando índices de cobertura e filtragem adicional. Sem benefícios significativos de desempenho. Possíveis recomendações podem incluir:

  1. Filtragem adicional
  2. Maneiras de reescrever, posso ver buscas de heap lá, então executarei VACUUM em breve, então, exceto VACUUM, o que pode ser feito? Agradeço sua ajuda

https://explain.dalibo.com/plan/e6b0c5757962095a

Versão PG: PostgreSQL 14.7

A pergunta:

SELECT
    oh.order_header_id AS SfmId,
    oh.status AS Status,
    oh.case_type AS CaseType,
    oh.partner_id AS CompanyId,
    oh.date_created AS DateCreated,
    oh.update_date_utc AS DateUpdated,
    oh.contact_id AS DoctorId,
    scan_detail.due_date AS DueDate,
    lab_link.partner_id AS LabId,
    COALESCE(milling_site_link.partner_id, -1) AS MillingSiteId,
    COALESCE(int_site_link.partner_id, -1) AS InterpretationSiteId,
    oh.order_tags AS OrderTags,
    oh.patient_guid AS PatientGuid,
    oh.rx_id AS RxId,
    FALSE AS IsConventional,
    COALESCE(prev_wo.work_type, -1) AS PreviousBowId,
    -1 AS LastDetailsId,
    oh.last_work_order_id AS LastWorkOrderSfmId,
    wo.date_created AS LastWorkOrderDateCreated,
    wo.date_updated AS LastWorkOrderDateUpdated,
    oh.direct_to_lab_status AS IsDirectToLab,
    wo.resource_id AS LastResourceId,
    wo.resource_type AS LastResourceTypeId,
    oh.scan_info AS ScanInfo,
    oh.extended_info AS ExtendedInfo,
    oh.file_upload_report AS FileUploadReport,
    wo.status AS LastWorkOrderStatus,
    wo.work_type AS LastBowId,
    wo.order_detail_id AS LastDetailsSfmId,
    od.due_date AS LastDetailsDueDate,
    -1 AS LastWorkOrderId,
    wo.status AS LastWorkOrderStatus,
    oh.order_code AS OrderCode,
    od.date_created AS LastDetailsDateCreated
FROM
    tab1 cpl
    LEFT JOIN tab2 oh ON oh.order_header_id = cpl.order_header_id
    LEFT JOIN LATERAL (
        SELECT
            due_date
        FROM
            tab3 scan_detail
        WHERE
            scan_detail.order_header_id = oh.order_header_id
            AND EXISTS (
                SELECT
                    1
                FROM
                    tab4 ctdc2
                WHERE
                    ctdc2.detail_type = scan_detail.item
                    AND ctdc2.detail_category = 1
            )
        LIMIT 1
    ) AS scan_detail ON TRUE
    LEFT JOIN LATERAL (
        SELECT
            partner_id
        FROM
            tab1 lab_link
        WHERE
            lab_link.order_header_id = oh.order_header_id
            AND lab_link.partner_type = 300
        LIMIT 1
    ) AS lab_link ON TRUE
    LEFT JOIN LATERAL (
        SELECT
            partner_id
        FROM
            tab1 milling_site_link
        WHERE
            milling_site_link.order_header_id = oh.order_header_id
            AND milling_site_link.partner_type = 500
        LIMIT 1
    ) AS milling_site_link ON TRUE
    LEFT JOIN LATERAL (
        SELECT
            partner_id
        FROM
            tab1 int_site_link
        WHERE
            int_site_link.order_header_id = oh.order_header_id
            AND int_site_link.partner_type = 1100
        LIMIT 1
    ) AS int_site_link ON TRUE
    INNER JOIN tab5 wo ON oh.last_work_order_id = wo.work_order_id
    INNER JOIN tab3 od ON oh.order_header_id = od.order_header_id AND wo.order_detail_id = od.order_detail_id
    LEFT JOIN LATERAL (
        SELECT
            *
        FROM
            tab5 prev_wo
        WHERE
            prev_wo.work_order_id = wo.created_by_work_order
        LIMIT 1
    ) AS prev_wo ON TRUE
WHERE
    cpl.partner_id = 8133
    AND cpl.partner_type = ANY (VALUES (200), (500), (1900), (2700))
    AND wo.partner_id != 8133
    AND (
        EXISTS (
            SELECT
                1
            FROM
                tab2 oh2
                INNER JOIN tab3 od2 ON oh2.order_header_id = od2.order_header_id
                INNER JOIN tab5 wo2 ON wo2.order_detail_id = od2.order_detail_id
            WHERE
                oh2.order_header_id = oh.order_header_id
                AND wo2.work_order_id != oh2.last_work_order_id
                AND wo2.partner_id = 8133
                AND wo2.date_updated > (NOW() AT TIME ZONE 'UTC' + INTERVAL '-90 days')
                AND wo2.work_type <> 131
            LIMIT 1
        )
        OR (
            101 = ANY (VALUES (102))
            AND lab_link.partner_id = 8133
        )
    )
    AND (
        wo.work_type > 0
        OR (
            (
                wo.work_type = -1
                OR wo.status <> 1
            )
            AND wo.date_updated > (NOW() AT TIME ZONE 'UTC' + INTERVAL '-7 days')
        )
    )
LIMIT 1500;

Aqui está o plano de consulta com

(ANALISAR, BUFFERS, CONFIGURAÇÕES, FORMATAR TEXTO)

Também em explica.depesz.com https://explain.depesz.com/s/j73P#html

Limit  (cost=1771.30..248159.49 rows=1 width=1206) (actual time=47.173..548.541 rows=1500 loops=1)
  Buffers: shared hit=776970
  ->  Nested Loop Semi Join  (cost=1771.30..248159.49 rows=1 width=1206) (actual time=47.172..548.291 rows=1500 loops=1)
        Join Filter: (cpl.partner_type = "*VALUES*".column1)
        Rows Removed by Join Filter: 7412
        Buffers: shared hit=776970
        ->  Nested Loop Left Join  (cost=1771.30..248159.39 rows=1 width=1197) (actual time=46.221..543.890 rows=2978 loops=1)
              Buffers: shared hit=776970
              ->  Nested Loop Left Join  (cost=1770.74..248150.79 rows=1 width=1209) (actual time=46.206..518.898 rows=2978 loops=1)
                    Buffers: shared hit=762079
                    ->  Nested Loop Left Join  (cost=1770.31..248142.30 rows=1 width=1205) (actual time=46.194..506.935 rows=2978 loops=1)
                          Buffers: shared hit=752105
                          ->  Nested Loop Left Join  (cost=1769.88..248133.80 rows=1 width=1201) (actual time=46.179..492.135 rows=2978 loops=1)
                                Filter: ((SubPlan 1) OR ((hashed SubPlan 3) AND (lab_link.partner_id = 8133)))
                                Rows Removed by Filter: 2649
                                Buffers: shared hit=740174
                                ->  Nested Loop Left Join  (cost=1769.43..247893.45 rows=1 width=1197) (actual time=13.117..282.515 rows=5627 loops=1)
                                      Buffers: shared hit=596990
                                      ->  Gather  (cost=1769.00..247875.58 rows=1 width=1189) (actual time=13.069..214.075 rows=5627 loops=1)
                                            Workers Planned: 2
                                            Workers Launched: 2
                                            Buffers: shared hit=570677
                                            ->  Nested Loop  (cost=769.00..246875.48 rows=1 width=1189) (actual time=9.170..289.074 rows=1905 loops=3)
                                                  Join Filter: (oh.tab2_id = od.tab2_id)
                                                  Buffers: shared hit=570677
                                                  ->  Nested Loop  (cost=768.57..236676.59 rows=20408 width=1189) (actual time=9.146..276.915 rows=1905 loops=3)
                                                        Buffers: shared hit=547781
                                                        ->  Nested Loop  (cost=768.00..209231.31 rows=25330 width=1127) (actual time=9.017..152.212 rows=19269 loops=3)
                                                              Buffers: shared hit=258744
                                                              ->  Parallel Bitmap Heap Scan on tab1 cpl  (cost=767.57..66251.14 rows=25330 width=20) (actual time=8.989..41.637 rows=19269 loops=3)
                                                                    Recheck Cond: (partner_id = 8133)
                                                                    Heap Blocks: exact=5996
                                                                    Buffers: shared hit=27514
                                                                    ->  Bitmap Index Scan on ix_tab1_partner_id_partner_type  (cost=0.00..752.37 rows=60792 width=0) (actual time=7.558..7.558 rows=60795 loops=1)
                                                                          Index Cond: (partner_id = 8133)
                                                                          Buffers: shared hit=83
                                                              ->  Index Scan using "PK_tab2" on tab2 oh  (cost=0.43..5.64 rows=1 width=1107) (actual time=0.005..0.005 rows=1 loops=57807)
                                                                    Index Cond: (tab2_id = cpl.tab2_id)
                                                                    Buffers: shared hit=231230
                                                        ->  Index Scan using "PK_tab5" on tab5 wo  (cost=0.56..1.08 rows=1 width=78) (actual time=0.006..0.006 rows=0 loops=57807)
                                                              Index Cond: (tab5_id = oh.last_tab5_id)
                                                              Filter: ((partner_id <> 8133) AND ((work_type > 0) OR (((work_type = '-1'::integer) OR (status <> 1)) AND (date_updated > ((now() AT TIME ZONE 'UTC'::text) + '-7 days'::interval)))))
                                                              Rows Removed by Filter: 1
                                                              Buffers: shared hit=289037
                                                  ->  Index Scan using "PK_tab3" on tab3 od  (cost=0.43..0.49 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=5715)
                                                        Index Cond: (tab3_id = wo.tab3_id)
                                                        Buffers: shared hit=22879
                                      ->  Limit  (cost=0.43..17.84 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5627)
                                            Buffers: shared hit=26313
                                            ->  Nested Loop Semi Join  (cost=0.43..17.84 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5627)
                                                  Join Filter: (scan_detail.item = ctdc2.detail_type)
                                                  Rows Removed by Join Filter: 19
                                                  Buffers: shared hit=26313
                                                  ->  Index Scan using "IX_tab3_tab2_id" on tab3 scan_detail  (cost=0.43..16.48 rows=3 width=12) (actual time=0.005..0.006 rows=2 loops=5627)
                                                        Index Cond: (tab2_id = oh.tab2_id)
                                                        Buffers: shared hit=25878
                                                  ->  Materialize  (cost=0.00..1.32 rows=1 width=4) (actual time=0.000..0.001 rows=9 loops=12647)
                                                        Buffers: shared hit=1
                                                        ->  Seq Scan on tab4 ctdc2  (cost=0.00..1.31 rows=1 width=4) (actual time=0.006..0.010 rows=14 loops=1)
                                                              Filter: (detail_category = 1)
                                                              Rows Removed by Filter: 11
                                                              Buffers: shared hit=1
                                ->  Limit  (cost=0.43..8.47 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=5627)
                                      Buffers: shared hit=22214
                                      ->  Index Only Scan using ix_tab2_id_partner_id_partner_type on tab1 lab_link  (cost=0.43..8.47 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=5627)
                                            Index Cond: ((tab2_id = oh.tab2_id) AND (partner_type = 300))
                                            Heap Fetches: 5199
                                            Buffers: shared hit=22214
                                SubPlan 1
                                  ->  Nested Loop  (cost=1.30..231.85 rows=1 width=0) (actual time=0.030..0.030 rows=1 loops=5627)
                                        Join Filter: (wo2.tab5_id <> oh2.last_tab5_id)
                                        Buffers: shared hit=120970
                                        ->  Nested Loop  (cost=0.87..223.38 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=5627)
                                              Buffers: shared hit=109058
                                              ->  Index Scan using "IX_tab3_tab2_id" on tab3 od2  (cost=0.43..16.48 rows=3 width=32) (actual time=0.003..0.004 rows=3 loops=5627)
                                                    Index Cond: (tab2_id = oh.tab2_id)
                                                    Buffers: shared hit=28948
                                              ->  Index Scan using "IX_tab5_tab3_id" on tab5 wo2  (cost=0.44..68.96 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=18155)
                                                    Index Cond: (tab3_id = od2.tab3_id)
                                                    Filter: ((work_type <> 131) AND (partner_id = 8133) AND (date_updated > ((now() AT TIME ZONE 'UTC'::text) + '-90 days'::interval)))
                                                    Rows Removed by Filter: 2
                                                    Buffers: shared hit=79660
                                        ->  Index Scan using "PK_tab2" on tab2 oh2  (cost=0.43..8.45 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=2978)
                                              Index Cond: (tab2_id = oh.tab2_id)
                                              Buffers: shared hit=11912
                                SubPlan 3
                                  ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
                          ->  Limit  (cost=0.43..8.47 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2978)
                                Buffers: shared hit=11931
                                ->  Index Only Scan using ix_tab2_id_partner_id_partner_type on tab1 milling_site_link  (cost=0.43..8.47 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2978)
                                      Index Cond: ((tab2_id = oh.tab2_id) AND (partner_type = 500))
                                      Heap Fetches: 2993
                                      Buffers: shared hit=11931
                    ->  Limit  (cost=0.43..8.47 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2978)
                          Buffers: shared hit=9974
                          ->  Index Only Scan using ix_tab2_id_partner_id_partner_type on tab1 int_site_link  (cost=0.43..8.47 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2978)
                                Index Cond: ((tab2_id = oh.tab2_id) AND (partner_type = 1100))
                                Heap Fetches: 1056
                                Buffers: shared hit=9974
              ->  Limit  (cost=0.56..8.58 rows=1 width=134) (actual time=0.008..0.008 rows=1 loops=2978)
                    Buffers: shared hit=14891
                    ->  Index Scan using "PK_tab5" on tab5 prev_wo  (cost=0.56..8.58 rows=1 width=134) (actual time=0.007..0.007 rows=1 loops=2978)
                          Index Cond: (tab5_id = wo.created_by_tab5)
                          Buffers: shared hit=14890
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=4 width=4) (actual time=0.000..0.001 rows=3 loops=2978)
Settings: effective_cache_size = '88445488kB', maintenance_io_concurrency = '1'
Planning:
  Buffers: shared hit=5660
Planning Time: 5.158 ms
Execution Time: 549.025 ms
postgresql

2 respostas

  1. Best Answer

    Além de vários outros possíveis problemas (alguns dos quais já foram resolvidos) - principalmente configuração do servidor e otimização de índice - há um problema com junções.

    Conto 9 itens de junção (simples e subconsultas), o que excede a configuração padrão de 8 para from_collapse_limite join_collapse_limit. Não tenho certeza de como isso acontece exatamente. Mas como a ordem das junções é confusa em cima de um incorretoLEFT JOIN , isso não será bom.
    Esta sequência é um absurdo:

    ...
LEFT JOIN tab2 oh ...
LEFT JOIN ...
...
JOIN tab5 wo ON oh.last_work_order_id = wo.work_order_id
JOIN tab3 od ON oh.order_header_id = od.order_header_id AND wo.order_detail_id = od.order_detail_id
...
WHERE ...
AND wo.partner_id != 8133

    O filtro ativado woforça o LEFT OUTER JOINa agir como um arquivo INNER JOIN. Ver:

    Mas como os limites de "colapso" são excedidos, a sequência incorreta de junções é congelada (pelo menos até certo ponto). Tente esta consulta equivalente (sem alterar os limites mencionados):

    SELECT oh.order_header_id AS SfmId
     , oh.status AS Status
     , oh.case_type AS CaseType
     , oh.partner_id AS CompanyId
     , oh.date_created AS DateCreated
     , oh.update_date_utc AS DateUpdated
     , oh.contact_id AS DoctorId
--   , scan_detail.due_date AS DueDate
     , (SELECT due_date
        FROM   tab3 s
        WHERE  s.order_header_id = oh.order_header_id
        AND    EXISTS (
           SELECT FROM tab4 ctdc2
           WHERE  ctdc2.detail_type = s.item
           AND    ctdc2.detail_category = 1
            )
        LIMIT  1) AS DueDate
--   , lab_link.partner_id AS LabId
     ,          (SELECT partner_id FROM tab1 l WHERE l.order_header_id = oh.order_header_id AND l.partner_type =  300 LIMIT 1)      AS LabId
--   , COALESCE(milling_site_link.partner_id, -1) AS MillingSiteId
     , COALESCE((SELECT partner_id FROM tab1 m WHERE m.order_header_id = oh.order_header_id AND m.partner_type =  500 LIMIT 1), -1) AS MillingSiteId
--   , COALESCE(int_site_link.partner_id, -1) AS InterpretationSiteId
     , COALESCE((SELECT partner_id FROM tab1 i WHERE i.order_header_id = oh.order_header_id AND i.partner_type = 1100 LIMIT 1), -1) AS InterpretationSiteId
     , oh.order_tags AS OrderTags
     , oh.patient_guid AS PatientGuid
     , oh.rx_id AS RxId
     , false AS IsConventional
--   , COALESCE(prev_wo.work_type, -1) AS PreviousBowId
     , COALESCE((SELECT p.work_type FROM tab5 p WHERE p.work_order_id = wo.created_by_work_order LIMIT 1), -1) AS PreviousBowId
     , -1 AS LastDetailsId
     , oh.last_work_order_id AS LastWorkOrderSfmId
     , wo.date_created AS LastWorkOrderDateCreated
     , wo.date_updated AS LastWorkOrderDateUpdated
     , oh.direct_to_lab_status AS IsDirectToLab
     , wo.resource_id AS LastResourceId
     , wo.resource_type AS LastResourceTypeId
     , oh.scan_info AS ScanInfo
     , oh.extended_info AS ExtendedInfo
     , oh.file_upload_report AS FileUploadReport
     , wo.status AS LastWorkOrderStatus
     , wo.work_type AS LastBowId
     , wo.order_detail_id AS LastDetailsSfmId
     , od.due_date AS LastDetailsDueDate
     , -1 AS LastWorkOrderId
     , wo.status AS LastWorkOrderStatus
     , oh.order_code AS OrderCode
     , od.date_created AS LastDetailsDateCreated
FROM   tab1 cpl
JOIN   tab2 oh USING (order_header_id)  -- !!!
JOIN   tab5 wo ON oh.last_work_order_id = wo.work_order_id
JOIN   tab3 od ON oh.order_header_id = od.order_header_id
              AND wo.order_detail_id = od.order_detail_id
/*
LEFT   JOIN LATERAL (
   SELECT due_date
   FROM   tab3 scan_detail
   WHERE  scan_detail.order_header_id = oh.order_header_id
   AND    EXISTS (
      SELECT FROM tab4 ctdc2
      WHERE  ctdc2.detail_type = scan_detail.item
      AND    ctdc2.detail_category = 1
      )
   LIMIT  1
   ) scan_detail ON true
LEFT   JOIN LATERAL (
   SELECT partner_id
   FROM   tab1 lab_link
   WHERE  lab_link.order_header_id = oh.order_header_id
   AND    lab_link.partner_type = 300
   LIMIT  1
   ) lab_link ON true
LEFT   JOIN LATERAL (
   SELECT partner_id
   FROM   tab1 milling_site_link
   WHERE  milling_site_link.order_header_id = oh.order_header_id
   AND    milling_site_link.partner_type = 500
   LIMIT  1
   ) milling_site_link ON true
LEFT   JOIN LATERAL (
   SELECT partner_id
   FROM   tab1 int_site_link
   WHERE  int_site_link.order_header_id = oh.order_header_id
   AND    int_site_link.partner_type = 1100
   LIMIT  1
   ) int_site_link ON true
LEFT   JOIN LATERAL (
   SELECT *
   FROM   tab5 prev_wo
   WHERE  prev_wo.work_order_id = wo.created_by_work_order
   LIMIT  1
   ) prev_wo ON true
*/
WHERE  cpl.partner_id = 8133
AND    cpl.partner_type = ANY (VALUES (200), (500), (1900), (2700))
AND    wo.partner_id <> 8133
AND   (
   EXISTS (
      SELECT
      FROM   tab2 oh2  -- redundant?
      JOIN   tab3 od2 ON od2.order_header_id = oh2.order_header_id
      JOIN   tab5 wo2 ON wo2.order_detail_id = od2.order_detail_id
      WHERE  oh2.order_header_id = oh.order_header_id
      AND    wo2.work_order_id <> oh2.last_work_order_id
      AND    wo2.partner_id = 8133
      AND    wo2.date_updated > (NOW() AT TIME ZONE 'UTC' + INTERVAL '-90 days')
      AND    wo2.work_type <> 131
      -- LIMIT  1  -- noise here !!!
   )
-- OR  lab_link.partner_id = 8133  AND 101 = ANY (VALUES (102))  -- ??? noise: always false !!!
   )
AND  (
   wo.work_type > 0
   OR (wo.work_type = -1 OR wo.status <> 1)
       AND wo.date_updated > NOW() AT TIME ZONE 'UTC' + INTERVAL '-7 days'
   )
LIMIT 1500;

    Transformei todas as instâncias LEFT JOIN LATERAL ... LIMIT 1 .. ON trueem subconsultas correlacionadas na SELECTlista. Isso deve deixá-lo abaixo dos limites de junção - o que não deve importar muito depois de corrigir as LEFT JOINjunções erradas e reordenar. Espero um plano de consulta (muito) melhor.

    Comentei as peças substituídas.
    Além disso, também comentei esse ruído:

    OR  lab_link.partner_id = 8133  AND 101 = ANY (VALUES (102))

    Você ainda pode precisar dele para outros valores de parâmetros! (?)

    Se tab2.order_header_idfor PK ou UNIQUE, podemos simplificar ainda mais. Informações faltantes.

    O último ORparece feio. Dividir em duas UNIONconsultas pode ajudar. Ver:

    Also, LIMIT 1 without ORDER BY produces arbitrary results, which is suspicious. There may be lurking problems ...

    • 4

  2. Seu plano mais rápido (j73P) usa apenas dois trabalhadores paralelos e pode se beneficiar de mais deles. Portanto, tente aumentar max_parallel_workers_per_gather (que presumo que seja o padrão 2, já que não apareceu na linha 'Configurações:' com um valor não padrão). Seu plano um pouco mais simples, porém mais lento (58Eq), não usa trabalhadores paralelos (e pode ser por isso que é mais lento), mas não sei por que isso acontece. Talvez o planejador simplesmente não ache que eles serão úteis, ou talvez haja algum recurso que os iniba. Sem ver o texto dessa consulta, é difícil saber.

    Obviamente, usar mais trabalhadores paralelos só é um benefício se você tiver recursos subutilizados.

    Um dos principais sumidouros de tempo (mas não o dominante, pois não existe um único dominante) é este:

    Index Cond: (tab3_id = od2.tab3_id)
Filter: ((work_type <> 131) AND (partner_id = 8,133) AND (date_updated > ((now() AT TIME ZONE 'UTC'::text) + '-90 days'::interval)))
Rows Removed by Filter: 2

    Agora não sabemos qual dos componentes dessa lógica de filtro é responsável por descartar 2/3 das linhas descartadas. Mas poderia ajudar se o descarte fosse feito dentro do índice, sem visitar a mesa. isso provavelmente significaria um índice de várias colunas, por exemplo, (tab3_id, partner_id). Uma estratégia semelhante também pode valer a pena em outras partes do plano, mas o uso da lógica OR pode tornar isso inviável.

    Alternativamente, talvez isso possa ser feito como hash join em vez de um loop aninhado, pré-carregando todas as partes que podem ser elegíveis para uma correspondência e, em seguida, fazendo hash delas em tab3_id. Para apoiar isso de forma eficiente, você gostaria de um índice em(partner_id, date_updated, worker_type, tab3_id)

    Pode haver algumas opções de cima para baixo para reescrever a consulta ou evitá-la totalmente, mas a consulta em si não é autoexplicativa. Portanto, alguma descrição de alto nível do que a consulta está fazendo e por que é necessário fazê-la pode ser útil. Por exemplo, por que uma consulta desta natureza precisa ser concluída em menos de meio segundo? Se você estiver compilando uma lista de tarefas para alguém trabalhar, certamente demorará muito para concluir 1.500 tarefas, então com que frequência essa consulta pode ser necessária? Ou se a lista de tarefas não for tão longa, talvez o LIMIT não precise ser definido como 1.500 para construí-la.

    • 2

relate perguntas