explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MHvz

Settings
# exclusive inclusive rows x rows loops node
1. 0.786 3,321.088 ↑ 225.4 525 1

Unique (cost=21,658,798.95..21,659,390.67 rows=118,344 width=1,146) (actual time=3,316.957..3,321.088 rows=525 loops=1)

2.          

CTE hier

3. 0.719 2.238 ↑ 56.0 3,840 1

Recursive Union (cost=1.20..11,337.02 rows=215,166 width=64) (actual time=0.034..2.238 rows=3,840 loops=1)

4. 0.128 0.205 ↓ 1.0 666 1

Hash Join (cost=1.20..18.30 rows=656 width=48) (actual time=0.033..0.205 rows=666 loops=1)

  • Hash Cond: (oh."OrganizationStructureID" = ost."OrganizationStructureID")
5. 0.054 0.054 ↓ 1.0 666 1

Seq Scan on "OrganizationHierarchy" oh (cost=0.00..14.56 rows=656 width=24) (actual time=0.005..0.054 rows=666 loops=1)

6. 0.020 0.023 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=4) (actual time=0.023..0.023 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.003 0.003 ↑ 1.0 9 1

Seq Scan on "OrganizationStructure" ost (cost=0.00..1.09 rows=9 width=4) (actual time=0.001..0.003 rows=9 loops=1)

8. 0.930 1.314 ↑ 40.6 529 6

Hash Join (cost=22.75..701.54 rows=21,451 width=64) (actual time=0.046..0.219 rows=529 loops=6)

  • Hash Cond: (hier_1."OrganizationHierarchyID" = oh_1."ParentOrganizationHierarchyID")
9. 0.192 0.192 ↑ 10.2 640 6

WorkTable Scan on hier hier_1 (cost=0.00..131.20 rows=6,560 width=44) (actual time=0.000..0.032 rows=640 loops=6)

10. 0.111 0.192 ↓ 1.0 665 1

Hash (cost=14.56..14.56 rows=655 width=28) (actual time=0.192..0.192 rows=665 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
11. 0.081 0.081 ↓ 1.0 665 1

Seq Scan on "OrganizationHierarchy" oh_1 (cost=0.00..14.56 rows=655 width=28) (actual time=0.002..0.081 rows=665 loops=1)

  • Filter: ("ParentOrganizationHierarchyID" IS NOT NULL)
  • Rows Removed by Filter: 1
12.          

CTE region

13. 0.026 4.933 ↑ 35.9 30 1

Hash Join (cost=139.47..5,022.40 rows=1,076 width=36) (actual time=0.930..4.933 rows=30 loops=1)

  • Hash Cond: (ost_1."OrganizationID" = org."OrganizationID")
14. 0.281 4.056 ↑ 35.9 30 1

Hash Join (cost=1.12..4,881.23 rows=1,076 width=40) (actual time=0.058..4.056 rows=30 loops=1)

  • Hash Cond: (hier_2."OrganizationStructureID" = ost_1."OrganizationStructureID")
15. 3.769 3.769 ↑ 56.0 3,840 1

CTE Scan on hier hier_2 (cost=0.00..4,303.32 rows=215,166 width=40) (actual time=0.034..3.769 rows=3,840 loops=1)

16. 0.003 0.006 ↓ 2.0 2 1

Hash (cost=1.11..1.11 rows=1 width=8) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.003 0.003 ↓ 2.0 2 1

Seq Scan on "OrganizationStructure" ost_1 (cost=0.00..1.11 rows=1 width=8) (actual time=0.002..0.003 rows=2 loops=1)

  • Filter: ("StructureName" = 'Division'::text)
  • Rows Removed by Filter: 7
18. 0.454 0.851 ↓ 1.0 3,939 1

Hash (cost=89.82..89.82 rows=3,882 width=4) (actual time=0.851..0.851 rows=3,939 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 171kB
19. 0.397 0.397 ↓ 1.0 3,939 1

Seq Scan on "Organization" org (cost=0.00..89.82 rows=3,882 width=4) (actual time=0.002..0.397 rows=3,939 loops=1)

20. 28.445 3,320.302 ↑ 9.4 12,600 1

Sort (cost=21,642,439.53..21,642,735.39 rows=118,344 width=1,146) (actual time=3,316.956..3,320.302 rows=12,600 loops=1)

  • Sort Key: e."EventID
  • Sort Method: external merge Disk: 8880kB
21. 100.503 3,291.857 ↑ 9.4 12,600 1

Hash Join (cost=7,921.72..21,513,544.50 rows=118,344 width=1,146) (actual time=9.575..3,291.857 rows=12,600 loops=1)

  • Hash Cond: (hier."regionID" = region.id)
22. 10.617 36.405 ↓ 1.1 25,200 1

Hash Join (cost=7,886.75..12,569.89 rows=21,997 width=1,477) (actual time=4.283..36.405 rows=25,200 loops=1)

  • Hash Cond: (eg."OrganizationHierarchyID" = hier."OrganizationHierarchyID")
23. 1.863 24.632 ↓ 76.4 4,200 1

Nested Loop Left Join (cost=52.85..2,173.59 rows=55 width=1,477) (actual time=2.617..24.632 rows=4,200 loops=1)

24. 1.437 18.569 ↓ 76.4 4,200 1

Hash Left Join (cost=52.57..2,156.98 rows=55 width=1,460) (actual time=2.611..18.569 rows=4,200 loops=1)

  • Hash Cond: (eg."OrganizationHierarchyID" = l."OrganizationHierarchyID")
25. 3.120 16.910 ↓ 76.4 4,200 1

Nested Loop Left Join (cost=3.59..2,107.85 rows=55 width=1,435) (actual time=2.381..16.910 rows=4,200 loops=1)

26. 0.739 9.590 ↓ 76.4 4,200 1

Nested Loop Left Join (cost=3.45..2,099.20 rows=55 width=1,427) (actual time=2.376..9.590 rows=4,200 loops=1)

  • Join Filter: (eg."WorkOrderID" = wod."WorkOrderID")
27. 0.063 4.651 ↓ 9.5 525 1

Nested Loop Left Join (cost=3.45..482.12 rows=55 width=1,431) (actual time=0.057..4.651 rows=525 loops=1)

28. 0.456 4.063 ↓ 9.5 525 1

Nested Loop Left Join (cost=3.17..463.82 rows=55 width=1,431) (actual time=0.052..4.063 rows=525 loops=1)

29. 0.120 3.082 ↓ 9.5 525 1

Nested Loop Left Join (cost=2.75..433.15 rows=55 width=1,431) (actual time=0.046..3.082 rows=525 loops=1)

30. 0.205 1.912 ↓ 9.5 525 1

Hash Join (cost=2.32..405.61 rows=55 width=1,427) (actual time=0.040..1.912 rows=525 loops=1)

  • Hash Cond: (e."EventStatusID" = es."EventStatusID")
31. 0.361 1.698 ↓ 8.3 525 1

Nested Loop (cost=1.14..404.17 rows=63 width=1,395) (actual time=0.024..1.698 rows=525 loops=1)

32. 0.126 0.287 ↓ 20.2 525 1

Nested Loop (cost=0.71..18.24 rows=26 width=279) (actual time=0.019..0.287 rows=525 loops=1)

33. 0.008 0.008 ↑ 1.0 1 1

Index Scan using "pk_WorkOrder" on "WorkOrder" wo (cost=0.29..8.30 rows=1 width=271) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: ("WorkOrderID" = 25029)
34. 0.153 0.153 ↓ 20.2 525 1

Index Scan using "IX_EventGroup_WorkOrder" on "EventGroup" eg (cost=0.42..9.68 rows=26 width=12) (actual time=0.010..0.153 rows=525 loops=1)

  • Index Cond: ("WorkOrderID" = 25029)
35. 1.050 1.050 ↑ 7.0 1 525

Index Scan using "Event_EventGroupID" on "Event" e (cost=0.42..14.77 rows=7 width=1,124) (actual time=0.002..0.002 rows=1 loops=525)

  • Index Cond: ("EventGroupID" = eg."EventGroupID")
  • Filter: ("deletedAt" IS NULL)
36. 0.005 0.009 ↑ 1.2 6 1

Hash (cost=1.10..1.10 rows=7 width=36) (actual time=0.009..0.009 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.004 0.004 ↑ 1.2 6 1

Seq Scan on "EventStatus" es (cost=0.00..1.10 rows=7 width=36) (actual time=0.003..0.004 rows=6 loops=1)

  • Filter: ("EventStatus" <> 'Cancelled'::text)
  • Rows Removed by Filter: 2
38. 1.050 1.050 ↑ 2.0 1 525

Index Scan using "IX_EventServiceItem_EventID" on "EventServiceItem" esi (cost=0.42..0.48 rows=2 width=8) (actual time=0.001..0.002 rows=1 loops=525)

  • Index Cond: (e."EventID" = "EventID")
39. 0.525 0.525 ↑ 1.0 1 525

Index Scan using "pk_ClientItem" on "ClientItem" ci (cost=0.42..0.56 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=525)

  • Index Cond: (esi."ClientItemID" = "ClientItemID")
40. 0.525 0.525 ↓ 0.0 0 525

Index Scan using "IX_EventDemoKitItem_EventID" on "EventDemoKitItem" edki (cost=0.28..0.31 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=525)

  • Index Cond: (e."EventID" = "EventID")
41. 0.591 4.200 ↓ 8.0 8 525

Materialize (cost=0.00..1,616.26 rows=1 width=4) (actual time=0.004..0.008 rows=8 loops=525)

42. 0.723 3.609 ↓ 8.0 8 1

Nested Loop Left Join (cost=0.00..1,616.26 rows=1 width=4) (actual time=2.317..3.609 rows=8 loops=1)

  • Join Filter: (wod."WorkOrderDPISID" = wode."WorkOrderDPISID")
  • Rows Removed by Join Filter: 11128
43. 2.134 2.134 ↓ 8.0 8 1

Seq Scan on "WorkOrderDPIS" wod (cost=0.00..1,568.28 rows=1 width=8) (actual time=2.127..2.134 rows=8 loops=1)

  • Filter: ("WorkOrderID" = 25029)
  • Rows Removed by Filter: 12619
44. 0.752 0.752 ↓ 1.0 1,391 8

Seq Scan on "WorkOrderDPISEnhancement" wode (cost=0.00..30.77 rows=1,377 width=8) (actual time=0.002..0.094 rows=1,391 loops=8)

45. 4.200 4.200 ↑ 1.0 1 4,200

Index Scan using "pk_EventDuration" on "EventDuration" ed (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=4,200)

  • Index Cond: (e."EventDurationID" = "EventDurationID")
46. 0.122 0.222 ↓ 1.0 582 1

Hash (cost=41.77..41.77 rows=577 width=25) (actual time=0.222..0.222 rows=582 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
47. 0.100 0.100 ↓ 1.0 582 1

Seq Scan on "Location" l (cost=0.00..41.77 rows=577 width=25) (actual time=0.003..0.100 rows=582 loops=1)

48. 4.200 4.200 ↑ 1.0 1 4,200

Index Scan using "pk_Client" on "Client" (cost=0.28..0.30 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=4,200)

  • Index Cond: ("ClientID" = ci."ClientID")
49. 0.584 1.156 ↑ 56.0 3,840 1

Hash (cost=4,303.32..4,303.32 rows=215,166 width=8) (actual time=1.156..1.156 rows=3,840 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 1085kB
50. 0.572 0.572 ↑ 56.0 3,840 1

CTE Scan on hier (cost=0.00..4,303.32 rows=215,166 width=8) (actual time=0.001..0.572 rows=3,840 loops=1)

51. 0.007 4.949 ↑ 35.9 30 1

Hash (cost=21.52..21.52 rows=1,076 width=36) (actual time=4.949..4.949 rows=30 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
52. 4.942 4.942 ↑ 35.9 30 1

CTE Scan on region (cost=0.00..21.52 rows=1,076 width=36) (actual time=0.931..4.942 rows=30 loops=1)

53.          

SubPlan (forHash Join)

54. 0.000 3,099.600 ↑ 1.0 1 12,600

Result (cost=170.79..170.80 rows=1 width=32) (actual time=0.246..0.246 rows=1 loops=12,600)

55.          

Initplan (forResult)

56. 12.600 3,099.600 ↑ 2.0 1 12,600

Unique (cost=170.78..170.79 rows=2 width=36) (actual time=0.245..0.246 rows=1 loops=12,600)

57. 12.600 3,087.000 ↑ 2.0 1 12,600

Sort (cost=170.78..170.78 rows=2 width=36) (actual time=0.245..0.245 rows=1 loops=12,600)

  • Sort Key: "Item_1"."ItemID
  • Sort Method: quicksort Memory: 25kB
58. 100.800 3,074.400 ↑ 2.0 1 12,600

Nested Loop (cost=1.42..170.77 rows=2 width=36) (actual time=0.243..0.244 rows=1 loops=12,600)

59. 12.600 75.600 ↑ 2.0 1 12,600

Nested Loop (cost=1.13..26.01 rows=2 width=20) (actual time=0.005..0.006 rows=1 loops=12,600)

60. 12.600 50.400 ↑ 2.0 1 12,600

Nested Loop (cost=0.85..25.35 rows=2 width=16) (actual time=0.004..0.004 rows=1 loops=12,600)

61. 25.200 25.200 ↑ 2.0 1 12,600

Index Scan using "IX_EventServiceItem_EventID" on "EventServiceItem" esi_1 (cost=0.42..8.47 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=12,600)

  • Index Cond: ("EventID" = e."EventID")
  • Filter: ("deletedAt" IS NULL)
62. 12.600 12.600 ↑ 1.0 1 12,600

Index Scan using "pk_ClientItem" on "ClientItem" ci_2 (cost=0.42..8.44 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=12,600)

  • Index Cond: ("ClientItemID" = esi_1."ClientItemID")
63. 12.600 12.600 ↑ 1.0 1 12,600

Index Only Scan using "IX_ItemDPIS_Item" on "ItemDPIS" id (cost=0.29..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12,600)

  • Index Cond: ("ItemID" = ci_2."ItemID")
  • Heap Fetches: 12600
64. 12.600 12.600 ↑ 1.0 1 12,600

Index Scan using "pk_Item" on "Item" "Item_1" (cost=0.29..0.31 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=12,600)

  • Index Cond: ("ItemID" = ci_2."ItemID")
65.          

SubPlan (forNested Loop)

66. 12.600 2,885.400 ↑ 1.0 1 12,600

Result (cost=72.06..72.07 rows=1 width=32) (actual time=0.229..0.229 rows=1 loops=12,600)

67.          

Initplan (forResult)

68. 0.000 2,872.800 ↓ 0.0 0 12,600

Unique (cost=72.05..72.06 rows=1 width=36) (actual time=0.228..0.228 rows=0 loops=12,600)

69. 12.600 2,872.800 ↓ 0.0 0 12,600

Sort (cost=72.05..72.05 rows=1 width=36) (actual time=0.228..0.228 rows=0 loops=12,600)

  • Sort Key: essi."EventServiceSubstituteItemID
  • Sort Method: quicksort Memory: 25kB
70. 0.000 2,860.200 ↓ 0.0 0 12,600

Nested Loop (cost=0.71..72.04 rows=1 width=36) (actual time=0.227..0.227 rows=0 loops=12,600)

71. 0.000 2,860.200 ↓ 0.0 0 12,600

Nested Loop (cost=0.42..71.73 rows=1 width=20) (actual time=0.227..0.227 rows=0 loops=12,600)

72. 2,860.200 2,860.200 ↓ 0.0 0 12,600

Seq Scan on "EventServiceSubstituteItem" essi (cost=0.00..63.29 rows=1 width=16) (actual time=0.227..0.227 rows=0 loops=12,600)

  • Filter: (("deletedAt" IS NULL) AND ("EventServiceItemID" = esi_1."EventServiceItemID"))
  • Rows Removed by Filter: 2983
73. 0.000 0.000 ↓ 0.0 0

Index Scan using "pk_ClientItem" on "ClientItem" ci_1 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: ("ClientItemID" = essi."SubstituteClientItemID")
74. 0.000 0.000 ↓ 0.0 0

Index Scan using "pk_Item" on "Item" (cost=0.29..0.31 rows=1 width=40) (never executed)

  • Index Cond: ("ItemID" = ci_1."ItemID")
75. 0.000 50.400 ↑ 1.0 1 12,600

Result (cost=10.83..10.84 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=12,600)

76.          

Initplan (forResult)

77. 12.600 50.400 ↓ 0.0 0 12,600

Unique (cost=10.82..10.83 rows=2 width=16) (actual time=0.004..0.004 rows=0 loops=12,600)

78. 12.600 37.800 ↓ 0.0 0 12,600

Sort (cost=10.82..10.82 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=12,600)

  • Sort Key: edki_1."DemoKitItemID
  • Sort Method: quicksort Memory: 25kB
79. 12.600 25.200 ↓ 0.0 0 12,600

Hash Right Join (cost=8.38..10.81 rows=2 width=16) (actual time=0.002..0.002 rows=0 loops=12,600)

  • Hash Cond: (dki."DemoKitItemID" = edki_1."DemoKitItemID")
80. 0.000 0.000 ↓ 0.0 0

Seq Scan on "DemoKitItem" dki (cost=0.00..1.94 rows=94 width=16) (never executed)

81. 0.000 12.600 ↓ 0.0 0 12,600

Hash (cost=8.35..8.35 rows=2 width=4) (actual time=0.001..0.001 rows=0 loops=12,600)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
82. 12.600 12.600 ↓ 0.0 0 12,600

Index Scan using "IX_EventDemoKitItem_EventID" on "EventDemoKitItem" edki_1 (cost=0.28..8.35 rows=2 width=4) (actual time=0.001..0.001 rows=0 loops=12,600)

  • Index Cond: (e."EventID" = "EventID")
  • Filter: ("deletedAt" IS NULL)
Planning time : 3.664 ms