explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xJIx

Settings
# exclusive inclusive rows x rows loops node
1. 0.778 3,235.447 ↑ 225.4 525 1

Unique (cost=21,608,502.75..21,609,094.47 rows=118,344 width=1,146) (actual time=3,231.410..3,235.447 rows=525 loops=1)

2.          

CTE hier

3. 0.739 2.260 ↑ 56.0 3,840 1

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

4. 0.128 0.189 ↓ 1.0 666 1

Hash Join (cost=1.20..18.30 rows=656 width=48) (actual time=0.016..0.189 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.008 ↑ 1.0 9 1

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

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

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

8. 0.947 1.332 ↑ 40.6 529 6

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

  • Hash Cond: (hier_1."OrganizationHierarchyID" = oh_1."ParentOrganizationHierarchyID")
9. 0.198 0.198 ↑ 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.033 rows=640 loops=6)

10. 0.106 0.187 ↓ 1.0 665 1

Hash (cost=14.56..14.56 rows=655 width=28) (actual time=0.187..0.187 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.021 4.800 ↑ 35.9 30 1

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

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

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

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

16. 0.002 0.004 ↓ 2.0 2 1

Hash (cost=1.11..1.11 rows=1 width=8) (actual time=0.004..0.004 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.001..0.002 rows=2 loops=1)

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

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

  • Buckets: 4096 Batches: 1 Memory Usage: 171kB
19. 0.399 0.399 ↓ 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.399 rows=3,939 loops=1)

20. 26.751 3,234.669 ↑ 9.4 12,600 1

Sort (cost=21,592,143.33..21,592,439.19 rows=118,344 width=1,146) (actual time=3,231.409..3,234.669 rows=12,600 loops=1)

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

Hash Join (cost=7,921.72..21,463,248.30 rows=118,344 width=1,146) (actual time=9.237..3,207.918 rows=12,600 loops=1)

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

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

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

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

24. 1.437 16.087 ↓ 76.4 4,200 1

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

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

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

26. 0.736 9.437 ↓ 76.4 4,200 1

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

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

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

28. 0.425 3.929 ↓ 9.5 525 1

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

29. 0.083 2.979 ↓ 9.5 525 1

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

30. 0.202 1.846 ↓ 9.5 525 1

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

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

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

32. 0.118 0.274 ↓ 20.2 525 1

Nested Loop (cost=0.71..18.24 rows=26 width=279) (actual time=0.017..0.274 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.148 0.148 ↓ 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.148 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.586 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.704 3.614 ↓ 8.0 8 1

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

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

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

  • Filter: ("WorkOrderID" = 25029)
  • Rows Removed by Filter: 12616
44. 0.736 0.736 ↓ 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.092 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.101 0.194 ↓ 1.0 582 1

Hash (cost=41.77..41.77 rows=577 width=25) (actual time=0.194..0.194 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.002..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.544 1.053 ↑ 56.0 3,840 1

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

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

51. 0.006 4.815 ↑ 35.9 30 1

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

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

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

53.          

SubPlan (forHash Join)

54. 12.600 3,036.600 ↑ 1.0 1 12,600

Result (cost=170.36..170.37 rows=1 width=32) (actual time=0.241..0.241 rows=1 loops=12,600)

55.          

Initplan (forResult)

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

Unique (cost=170.35..170.36 rows=2 width=36) (actual time=0.240..0.240 rows=1 loops=12,600)

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

Sort (cost=170.35..170.36 rows=2 width=36) (actual time=0.239..0.239 rows=1 loops=12,600)

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

Nested Loop (cost=1.42..170.34 rows=2 width=36) (actual time=0.238..0.238 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,822.400 ↑ 1.0 1 12,600

Result (cost=71.84..71.85 rows=1 width=32) (actual time=0.224..0.224 rows=1 loops=12,600)

67.          

Initplan (forResult)

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

Unique (cost=71.84..71.84 rows=1 width=36) (actual time=0.223..0.223 rows=0 loops=12,600)

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

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

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

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

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

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

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

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

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

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

Hash Right Join (cost=8.38..10.81 rows=2 width=16) (actual time=0.003..0.003 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. 12.600 25.200 ↓ 0.0 0 12,600

Hash (cost=8.35..8.35 rows=2 width=4) (actual time=0.002..0.002 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.344 ms