explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BZXu

Settings
# exclusive inclusive rows x rows loops node
1. 0.144 2,474.124 ↑ 1.2 66 1

Subquery Scan on T1 (cost=313,795.28..313,796.42 rows=79 width=411) (actual time=2,473.989..2,474.124 rows=66 loops=1)

2. 0.286 2,473.980 ↑ 1.2 66 1

Sort (cost=313,795.28..313,795.32 rows=79 width=15,780) (actual time=2,473.971..2,473.980 rows=66 loops=1)

  • Sort Key: (CASE WHEN ("T1_1"."estimatedShippingDate" IS NULL) THEN "T1_1"."quotedShippingDate" ELSE "T1_1"."estimatedShippingDate" END)
  • Sort Method: quicksort Memory: 59kB
3. 0.253 2,473.694 ↑ 1.2 66 1

Hash Join (cost=134,446.87..313,794.78 rows=79 width=15,780) (actual time=2,330.767..2,473.694 rows=66 loops=1)

  • Hash Cond: (("T1_1".id)::bigint = ("T1_7"."projectOrderId")::bigint)
4. 0.115 2,467.857 ↑ 1.2 66 1

Nested Loop (cost=134,183.19..313,530.80 rows=79 width=294) (actual time=2,325.151..2,467.857 rows=66 loops=1)

  • Join Filter: (("T1_1".id)::bigint = ("T1_8"."projectOrderID")::bigint)
5. 0.820 2,467.412 ↑ 1.2 66 1

Merge Join (cost=134,183.13..313,518.78 rows=80 width=237) (actual time=2,325.131..2,467.412 rows=66 loops=1)

  • Merge Cond: (("T1_1".id)::bigint = ("T1_17".id)::bigint)
6. 1.544 2,427.960 ↑ 1.2 66 1

Merge Left Join (cost=133,902.91..312,843.76 rows=80 width=221) (actual time=2,287.491..2,427.960 rows=66 loops=1)

  • Merge Cond: (("T1_1".id)::bigint = ("T1_5".result0_9)::bigint)
7. 0.566 664.496 ↑ 1.2 66 1

Merge Left Join (cost=132,902.60..133,091.06 rows=80 width=204) (actual time=661.560..664.496 rows=66 loops=1)

  • Merge Cond: (("T1_1".id)::bigint = ("T1_6".result0_15)::bigint)
8. 0.107 2.020 ↑ 1.2 66 1

Sort (cost=546.45..546.49 rows=80 width=187) (actual time=2.000..2.020 rows=66 loops=1)

  • Sort Key: "T1_1".id
  • Sort Method: quicksort Memory: 42kB
9. 0.089 1.913 ↑ 1.2 66 1

Nested Loop (cost=0.26..545.94 rows=80 width=187) (actual time=0.046..1.913 rows=66 loops=1)

10. 0.042 1.626 ↑ 1.5 66 1

Nested Loop (cost=0.20..536.52 rows=101 width=165) (actual time=0.036..1.626 rows=66 loops=1)

11. 0.092 1.320 ↑ 1.5 66 1

Nested Loop (cost=0.14..529.64 rows=101 width=157) (actual time=0.028..1.320 rows=66 loops=1)

12. 0.898 0.898 ↑ 1.5 66 1

Index Scan using "projectOrder_assemblyManufacturerId_idx" on "projectOrder" "T1_1" (cost=0.06..122.70 rows=101 width=157) (actual time=0.016..0.898 rows=66 loops=1)

  • Index Cond: (("assemblyManufacturerId")::bigint = '8063'::bigint)
  • Filter: (("deletedAt" IS NULL) AND ((status)::text <> 'canceled'::text) AND ((status)::text <> 'shipped'::text))
  • Rows Removed by Filter: 910
13. 0.330 0.330 ↑ 1.0 1 66

Index Scan using "projectQuote_pkey" on "projectQuote" "T1_2" (cost=0.08..4.03 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=66)

  • Index Cond: ((id)::bigint = ("T1_1"."projectQuoteID")::bigint)
14. 0.264 0.264 ↑ 1.0 1 66

Index Scan using "projectRevision_pkey" on "projectRevision" "T1_3" (cost=0.06..0.07 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=66)

  • Index Cond: ((id)::bigint = ("T1_2"."projectRevisionID")::bigint)
15. 0.198 0.198 ↑ 1.0 1 66

Index Scan using project_pkey on project "T1_4" (cost=0.06..0.09 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=66)

  • Index Cond: ((id)::bigint = ("T1_3"."projectID")::bigint)
  • Filter: ("deletedAt" IS NULL)
16. 0.738 661.910 ↑ 1.0 3,772 1

Subquery Scan on T1_6 (cost=132,356.15..132,542.48 rows=3,793 width=25) (actual time=615.862..661.910 rows=3,772 loops=1)

17. 41.647 661.172 ↑ 1.0 3,772 1

GroupAggregate (cost=132,356.15..132,531.11 rows=3,793 width=24) (actual time=615.860..661.172 rows=3,772 loops=1)

  • Group Key: "T1_9"."projectOrderID
18. 16.390 619.525 ↑ 2.6 31,511 1

Sort (cost=132,356.15..132,397.04 rows=81,788 width=24) (actual time=615.841..619.525 rows=31,511 loops=1)

  • Sort Key: "T1_9"."projectOrderID
  • Sort Method: quicksort Memory: 3,230kB
19. 10.892 603.135 ↑ 2.6 31,511 1

Gather (cost=1,041.83..131,021.40 rows=81,788 width=24) (actual time=3.373..603.135 rows=31,511 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 3.793 592.243 ↑ 3.2 10,504 3 / 3

Nested Loop (cost=41.83..121,842.60 rows=34,078 width=24) (actual time=1.985..592.243 rows=10,504 loops=3)

  • Join Filter: (("T1_11".id)::bigint = ("T1_12"."projectBomID")::bigint)
21. 13.598 531.019 ↑ 2.2 5,221 3 / 3

Nested Loop (cost=41.75..113,333.66 rows=11,566 width=32) (actual time=1.936..531.019 rows=5,221 loops=3)

22. 47.020 73.050 ↑ 1.2 55,546 3 / 3

Hash Anti Join (cost=41.66..5,452.30 rows=68,915 width=24) (actual time=1.743..73.050 rows=55,546 loops=3)

  • Hash Cond: (("T1_9".id)::bigint = ("T1_10"."previousId")::bigint)
23. 24.387 24.387 ↑ 1.2 56,599 3 / 3

Parallel Seq Scan on "bomSnapshot" "T1_9" (cost=0.00..5,166.72 rows=70,240 width=32) (actual time=0.011..24.387 rows=56,599 loops=3)

24. 0.866 1.643 ↑ 1.0 3,159 3 / 3

Hash (cost=30.54..30.54 rows=3,179 width=8) (actual time=1.642..1.643 rows=3,159 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 156kB
25. 0.777 0.777 ↑ 1.0 3,159 3 / 3

Seq Scan on "bomSnapshotHistory" "T1_10" (cost=0.00..30.54 rows=3,179 width=8) (actual time=0.022..0.777 rows=3,159 loops=3)

26. 444.371 444.371 ↓ 0.0 0 166,639 / 3

Index Scan using "projectBom_pkey" on "projectBom" "T1_11" (cost=0.08..1.57 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=166,639)

  • Index Cond: ((id)::bigint = ("T1_9"."bomId")::bigint)
  • Filter: ("footprintThroughHolePads" > 0)
  • Rows Removed by Filter: 1
27. 57.431 57.431 ↑ 9.0 2 15,663 / 3

Index Scan using "uniplex_projectBomRef_projectBomID0_idx" on "projectBomRef" "T1_12" (cost=0.09..0.67 rows=18 width=16) (actual time=0.008..0.011 rows=2 loops=15,663)

  • Index Cond: (("projectBomID")::bigint = ("T1_9"."bomId")::bigint)
28. 1.784 1,761.920 ↓ 1.4 5,474 1

Subquery Scan on T1_5 (cost=1,000.32..179,750.61 rows=3,793 width=25) (actual time=23.806..1,761.920 rows=5,474 loops=1)

29. 291.426 1,760.136 ↓ 1.4 5,474 1

GroupAggregate (cost=1,000.32..179,739.23 rows=3,793 width=24) (actual time=23.804..1,760.136 rows=5,474 loops=1)

  • Group Key: "T1_13"."projectOrderID
30. 35.978 1,468.710 ↓ 1.3 498,076 1

Nested Loop (cost=1,000.32..179,136.17 rows=394,452 width=24) (actual time=23.264..1,468.710 rows=498,076 loops=1)

31. 0.000 509.620 ↓ 1.1 153,852 1

Gather Merge (cost=1,000.23..133,794.07 rows=133,877 width=32) (actual time=23.244..509.620 rows=153,852 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
32. 37.895 723.593 ↑ 1.1 51,294 3 / 3

Nested Loop (cost=0.22..118,457.86 rows=55,782 width=32) (actual time=0.151..723.593 rows=51,294 loops=3)

33. 72.934 241.327 ↑ 1.2 55,546 3 / 3

Nested Loop Anti Join (cost=0.14..10,576.50 rows=68,915 width=24) (actual time=0.072..241.327 rows=55,546 loops=3)

34. 55.194 55.194 ↑ 1.2 56,599 3 / 3

Parallel Index Scan using "bomSnapshot_projectOrderID_idx" on "bomSnapshot" "T1_13" (cost=0.08..6,312.17 rows=70,240 width=32) (actual time=0.028..55.194 rows=56,599 loops=3)

35. 113.199 113.199 ↓ 0.0 0 169,798 / 3

Index Only Scan using "uniplex_bomSnapshotHistory_previousId0_idx" on "bomSnapshotHistory" "T1_14" (cost=0.06..0.06 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=169,798)

  • Index Cond: ("previousId" = ("T1_13".id)::bigint)
  • Heap Fetches: 649
36. 444.371 444.371 ↑ 1.0 1 166,639 / 3

Index Scan using "projectBom_pkey" on "projectBom" "T1_15" (cost=0.08..1.57 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=166,639)

  • Index Cond: ((id)::bigint = ("T1_13"."bomId")::bigint)
  • Filter: ("footprintSurfaceMountPads" > 0)
  • Rows Removed by Filter: 0
37. 923.112 923.112 ↑ 6.0 3 153,852

Index Scan using "uniplex_projectBomRef_projectBomID0_idx" on "projectBomRef" "T1_16" (cost=0.09..0.28 rows=18 width=16) (actual time=0.004..0.006 rows=3 loops=153,852)

  • Index Cond: (("projectBomID")::bigint = ("T1_15".id)::bigint)
38. 20.497 38.632 ↓ 1.0 5,833 1

GroupAggregate (cost=280.22..654.35 rows=5,826 width=16) (actual time=4.902..38.632 rows=5,833 loops=1)

  • Group Key: "T1_17".id
39. 1.533 18.135 ↓ 1.0 5,845 1

Merge Left Join (cost=280.22..631.04 rows=5,826 width=17) (actual time=4.890..18.135 rows=5,845 loops=1)

  • Merge Cond: (("T1_17".id)::bigint = ("T1_18"."orderId")::bigint)
40. 11.713 11.713 ↓ 1.0 5,834 1

Index Scan using "projectOrder_pkey" on "projectOrder" "T1_17" (cost=0.06..347.74 rows=5,826 width=8) (actual time=0.012..11.713 rows=5,834 loops=1)

  • Filter: ("deletedAt" IS NULL)
  • Rows Removed by Filter: 7
41. 0.038 4.889 ↑ 1.1 53 1

Sort (cost=280.16..280.19 rows=58 width=17) (actual time=4.875..4.889 rows=53 loops=1)

  • Sort Key: "T1_18"."orderId
  • Sort Method: quicksort Memory: 29kB
42. 4.851 4.851 ↑ 1.1 53 1

Seq Scan on issue "T1_18" (cost=0.00..279.82 rows=58 width=17) (actual time=3.215..4.851 rows=53 loops=1)

  • Filter: (("deletedAt" IS NULL) AND ('open'::text = (status)::text))
  • Rows Removed by Filter: 14,749
43. 0.330 0.330 ↑ 1.0 1 66

Index Scan using "uniplex_boardSnapshot_projectOrderID0_idx" on "boardSnapshot" "T1_8" (cost=0.06..0.15 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=66)

  • Index Cond: (("projectOrderID")::bigint = ("T1_17".id)::bigint)
44. 2.140 5.584 ↑ 1.0 5,797 1

Hash (cost=243.39..243.39 rows=5,797 width=18) (actual time=5.583..5.584 rows=5,797 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 381kB
45. 3.444 3.444 ↑ 1.0 5,797 1

Seq Scan on "boardOrder" "T1_7" (cost=0.00..243.39 rows=5,797 width=18) (actual time=0.007..3.444 rows=5,797 loops=1)