Optimize query with many joins

Question

The developers came asking to improve the query performance. I tried using CTEs instead of lateral joins and EXISTS, testing covering indexes, additional filtering. No significant performance benefits. Possible recommendations might include:

Additional filtering
Ways to rewrite

I can see heap fetches there, so I will execute VACUUM shortly. Apart from that, what can be done?

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

PG version: PostgreSQL 14.7

The query:

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;

Here is the query plan with (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT):
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

Erwin Brandstetter · Accepted Answer · 2024-01-05T23:27:40.840

In addition to various other possible issues (some of which have been addressed already) - most importantly server configuration and index optimization - there is an issue with joins.

I count 9 join items (plain & subqueries), which exceeds the default setting of 8 for from_collapse_limit and join_collapse_limit. Not sure how that plays out exactly. But since the order of joins is messed up on top of an incorrect LEFT JOIN, it will amount to no good.
This sequence is nonsense:

...
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

The filter on wo forces earlier instances of LEFT [OUTER] JOIN to act like [INNER] JOIN. See:

Join condition of a LEFT JOIN seems to be ignored for the left table?

But since "collapse" limits are exceeded, the bad sequence of joins is frozen (at least to some extent). Try this equivalent query (without changing the mentioned limits):

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;

I transformed all instances of LEFT JOIN LATERAL ... LIMIT 1 .. ON true into correlated subqueries in the SELECT list. This should get you below the join limits - which should not matter much after fixing the wrong LEFT JOIN and reordering joins. I expect a (much) better query plan.

I commented out the replaced parts.
Plus, I also commented out this noise:

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

You may still need it for other parameter values! (?)

If tab2.order_header_id is the PK or UNIQUE, we can simplify further. Missing info.

The last OR looks ugly. Splitting into two UNION queries may help. See:

Why is an OR statement slower than UNION?

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

score 2 · Answer 2 · answered Dec 26 '23 at 17:20

Your faster plan (j73P) is only using two parallel workers, and might be able to benefit from more of them. So, try increasing max_parallel_workers_per_gather (which I am assuming is at the default of 2, since it didn't show up on the 'Settings:' line with a non-default value). Your slightly simpler but slower plan (58Eq) isn't using parallel workers at all (and that might be why it is slower), but I don't know why that might be. Maybe the planner just doesn't think they will be useful, or maybe there is some feature inhibiting them. Without seeing the text of that query, it is hard to know.

Obviously using more parallel workers is only a benefit if you have underused resources.

One of the major time sinks (but not the dominant one, as there is no single dominant one) is this:

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

Now we don't know which of the components of that filter logic is responsible for throwing away the 2/3 of the rows that get thrown away. But it could help if the throwing away was done inside the index without visiting the table. that would likely mean a multicolumn index on, for example, (tab3_id, partner_id). A similar strategy might pay off in other parts of the plan as well but the use of OR logic might make that infeasible.

Alternatively, maybe this could be done as hash join instead of a nested loop, by preloading all the parts which might be eligible for a match and then hashing them on tab3_id. To support that efficiently, you would want an index on (partner_id, date_updated, worker_type, tab3_id)

There might be some top-down options for rewriting the query or avoiding it entirely, but the query itself is not self-explanatory. So some high level description of what the query is doing and why it is necessary to do it could be helpful. For example, why does a query of this nature need to complete in less than half a second? If you are compiling a task list for someone to work on, surely it is going to take a long while to get through 1500 tasks, so how often can such a query be needed? Or if the task list is not that long, then maybe the LIMIT doesn't need to be set as high as 1500 in order to construct it.

Optimize query with many joins

2 Answers2