explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Hop

Settings
# exclusive inclusive rows x rows loops node
1. 0.776 3,178.664 ↑ 225.4 525 1

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

2.          

CTE hier

3. 0.735 2.253 ↑ 56.0 3,840 1

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

4. 0.130 0.192 ↓ 1.0 666 1

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

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

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

6. 0.006 0.009 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=4) (actual time=0.009..0.009 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.002..0.003 rows=9 loops=1)

8. 0.942 1.326 ↑ 40.6 529 6

Hash Join (cost=22.75..701.54 rows=21,451 width=64) (actual time=0.046..0.221 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.936 ↑ 35.9 30 1

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

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

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

  • Hash Cond: (hier_2."OrganizationStructureID" = ost_1."OrganizationStructureID")
15. 3.775 3.775 ↑ 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.020..3.775 rows=3,840 loops=1)

16. 0.003 0.005 ↓ 2.0 2 1

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

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

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

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

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

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

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

20. 26.278 3,177.888 ↑ 9.4 12,600 1

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

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

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

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

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

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

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

24. 1.443 16.414 ↓ 76.4 4,200 1

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

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

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

26. 0.845 9.693 ↓ 76.4 4,200 1

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

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

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

28. 0.494 4.072 ↓ 9.5 525 1

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

29. 0.100 3.053 ↓ 9.5 525 1

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

30. 0.206 1.903 ↓ 9.5 525 1

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

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

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

32. 0.116 0.275 ↓ 20.2 525 1

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

33. 0.009 0.009 ↑ 1.0 1 1

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

  • Index Cond: ("WorkOrderID" = 25029)
34. 0.150 0.150 ↓ 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.009..0.150 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.010 ↑ 1.2 6 1

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

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

Seq Scan on "EventStatus" es (cost=0.00..1.10 rows=7 width=36) (actual time=0.003..0.005 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.516 4.200 ↓ 8.0 8 525

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

42. 0.719 3.684 ↓ 8.0 8 1

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

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

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

  • Filter: ("WorkOrderID" = 25029)
  • Rows Removed by Filter: 12618
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.104 0.197 ↓ 1.0 582 1

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

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

Seq Scan on "Location" l (cost=0.00..41.77 rows=577 width=25) (actual time=0.003..0.093 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.553 1.100 ↑ 56.0 3,840 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 1085kB
50. 0.547 0.547 ↑ 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.547 rows=3,840 loops=1)

51. 0.007 4.952 ↑ 35.9 30 1

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

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

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

53.          

SubPlan (forHash Join)

54. 12.600 2,986.200 ↑ 1.0 1 12,600

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

55.          

Initplan (forResult)

56. 12.600 2,973.600 ↑ 2.0 1 12,600

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

57. 12.600 2,961.000 ↑ 2.0 1 12,600

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

  • Sort Key: "Item_1"."ItemID
  • Sort Method: quicksort Memory: 25kB
58. 88.200 2,948.400 ↑ 2.0 1 12,600

Nested Loop (cost=1.42..170.77 rows=2 width=36) (actual time=0.234..0.234 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.003..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. 0.000 2,772.000 ↑ 1.0 1 12,600

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

67.          

Initplan (forResult)

68. 12.600 2,772.000 ↓ 0.0 0 12,600

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

69. 0.000 2,759.400 ↓ 0.0 0 12,600

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

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

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

71. 12.600 2,759.400 ↓ 0.0 0 12,600

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

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

Seq Scan on "EventServiceSubstituteItem" essi (cost=0.00..63.29 rows=1 width=16) (actual time=0.218..0.218 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. 12.600 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. 0.000 37.800 ↓ 0.0 0 12,600

Unique (cost=10.82..10.83 rows=2 width=16) (actual time=0.003..0.003 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.619 ms