explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ST0

Settings
# exclusive inclusive rows x rows loops node
1. 24.812 24.812 ↓ 2.0 2 1

CTE Scan on "empLawPrep" (cost=119.91..119.94 rows=1 width=1,514) (actual time=22.734..24.812 rows=2 loops=1)

2.          

CTE siteData

3. 0.004 0.022 ↑ 1.0 1 1

Result (cost=1.02..1.04 rows=1 width=1) (actual time=0.022..0.022 rows=1 loops=1)

4.          

Initplan (for Result)

5. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on "user" (cost=0.00..1.02 rows=1 width=4) (actual time=0.017..0.018 rows=1 loops=1)

  • Filter: (id = 576481439)
  • Rows Removed by Filter: 6
6.          

CTE empsRaw

7. 0.047 3.761 ↓ 2.0 2 1

Sort (cost=57.72..57.72 rows=1 width=874) (actual time=3.761..3.761 rows=2 loops=1)

  • Sort Key: employment.open DESC, employment."startDate", employment."position
  • Sort Method: quicksort Memory: 27kB
8.          

Initplan (for Sort)

9. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on "siteData" (cost=0.00..0.02 rows=1 width=1) (actual time=0.024..0.025 rows=1 loops=1)

10. 0.453 3.689 ↓ 2.0 2 1

Result (cost=0.00..57.69 rows=1 width=874) (actual time=3.467..3.689 rows=2 loops=1)

  • One-Time Filter: ($8 IS TRUE)
11. 0.014 0.014 ↓ 2.0 2 1

Seq Scan on employment (cost=0.00..11.12 rows=1 width=778) (actual time=0.009..0.014 rows=2 loops=1)

  • Filter: ((deleted IS NOT TRUE) AND ("readableForAsBorrower" @> '{576481439}'::integer[]))
12.          

SubPlan (for Result)

13. 3.144 3.144 ↑ 1.0 1 2

Index Scan using prospect_pkey on prospect (cost=0.14..10.98 rows=1 width=32) (actual time=1.567..1.572 rows=1 loops=2)

  • Index Cond: (id = employment."prospectID")
14. 0.020 0.020 ↓ 0.0 0 2

Index Scan using "prospect_employment_rating_employmentID_fkey_index" on prospect_employment_rating (cost=0.15..8.18 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=2)

  • Index Cond: ("employmentID" = employment.id)
15. 0.006 0.046 ↑ 1.0 1 2

Aggregate (cost=10.04..10.05 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=2)

16. 0.002 0.040 ↓ 0.0 0 2

Subquery Scan on rawdocuments (cost=10.01..10.03 rows=1 width=68) (actual time=0.020..0.020 rows=0 loops=2)

17. 0.028 0.038 ↓ 0.0 0 2

Sort (cost=10.01..10.02 rows=1 width=44) (actual time=0.019..0.019 rows=0 loops=2)

  • Sort Key: employment_contracts."uploadTime" DESC
  • Sort Method: quicksort Memory: 25kB
18. 0.004 0.010 ↓ 0.0 0 2

Bitmap Heap Scan on employment_contracts (cost=4.16..10.00 rows=1 width=44) (actual time=0.005..0.005 rows=0 loops=2)

  • Recheck Cond: ("employmentID" = employment.id)
  • Filter: ((deleted IS NOT TRUE) AND ("versionDeleted" IS NULL) AND permitted_employment(576481439, 'employment_contract_read'::text, id))
19. 0.006 0.006 ↓ 0.0 0 2

Bitmap Index Scan on employment_contracts_request_fkey_index (cost=0.00..4.16 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: ("employmentID" = employment.id)
20. 0.012 0.012 ↑ 1.0 1 2

Index Scan using private_site_owner_fkey_index on private_site (cost=0.14..8.16 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=2)

  • Index Cond: (owner = 576481439)
  • Filter: ((deleted IS NOT TRUE) AND (original = employment."siteLenderID"))
21. 0.000 0.000 ↓ 0.0 0

Index Scan using private_site_pkey on private_site private_site_1 (cost=0.14..8.16 rows=1 width=1) (never executed)

  • Index Cond: (id = employment."simulatedBorrower")
22.          

CTE privateSiteDataInvoled

23. 0.039 0.080 ↑ 1.0 1 1

Nested Loop Left Join (cost=4.70..13.78 rows=1 width=40) (actual time=0.079..0.080 rows=1 loops=1)

  • Join Filter: (orig.id = priv.original)
  • Rows Removed by Join Filter: 3
  • Filter: ((hashed SubPlan 10) OR (hashed SubPlan 11))
24. 0.005 0.005 ↑ 1.0 1 1

Index Scan using private_site_owner_fkey_index on private_site priv (cost=0.14..8.16 rows=1 width=40) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (owner = 576481439)
  • Filter: (deleted IS NOT TRUE)
25. 0.010 0.010 ↓ 4.0 4 1

Seq Scan on site orig (cost=0.00..1.01 rows=1 width=36) (actual time=0.010..0.010 rows=4 loops=1)

26.          

SubPlan (for Nested Loop Left Join)

27. 0.005 0.013 ↑ 100.0 1 1

HashAggregate (cost=0.78..2.03 rows=100 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Group Key: unnest(ARRAY["empsRaw"."simulatedBorrower", "empsRaw"."simulatedBorrowerDepartment"])
28. 0.004 0.008 ↑ 25.0 4 1

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual time=0.003..0.008 rows=4 loops=1)

29. 0.004 0.004 ↓ 2.0 2 1

CTE Scan on "empsRaw" (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.004 rows=2 loops=1)

30. 0.007 0.013 ↑ 33.3 3 1

HashAggregate (cost=0.78..2.03 rows=100 width=4) (actual time=0.012..0.013 rows=3 loops=1)

  • Group Key: unnest(ARRAY["empsRaw_1"."siteBorrowerID", "empsRaw_1"."siteLenderID", "empsRaw_1".client, "empsRaw_1"."departmentBorrower", "empsRaw_1"."departmentLender"])
31. 0.004 0.006 ↑ 10.0 10 1

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual time=0.004..0.006 rows=10 loops=1)

32. 0.002 0.002 ↓ 2.0 2 1

CTE Scan on "empsRaw" "empsRaw_1" (cost=0.00..0.02 rows=1 width=20) (actual time=0.002..0.002 rows=2 loops=1)

33.          

CTE empWithoutPrivIDsEtc

34. 3.786 3.870 ↓ 2.0 2 1

CTE Scan on "empsRaw" "empsRaw_2" (cost=0.00..0.15 rows=1 width=894) (actual time=3.857..3.870 rows=2 loops=1)

35.          

SubPlan (for CTE Scan)

36. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (original = "empsRaw_2".client)
37. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_1" (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (original = "empsRaw_2"."siteBorrowerID")
38. 0.084 0.084 ↑ 1.0 1 2

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_2" (cost=0.00..0.02 rows=1 width=4) (actual time=0.041..0.042 rows=1 loops=2)

  • Filter: (original = "empsRaw_2"."siteLenderID")
39. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_3" (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (original = "empsRaw_2"."departmentBorrower")
40. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_4" (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Filter: (original = "empsRaw_2"."departmentLender")
41.          

CTE empOrigUserInv

42. 0.204 0.268 ↑ 2.0 1 1

Hash Join (cost=1.82..5.63 rows=2 width=234) (actual time=0.262..0.268 rows=1 loops=1)

  • Hash Cond: ((unnest(("empWithoutPrivIDsEtc"."contactPersonBorrower" || "empWithoutPrivIDsEtc"."contactPersonLender"))) = user_1.id)
43. 0.012 0.041 ↑ 100.0 1 1

HashAggregate (cost=0.78..2.28 rows=100 width=4) (actual time=0.037..0.041 rows=1 loops=1)

  • Group Key: unnest(("empWithoutPrivIDsEtc"."contactPersonBorrower" || "empWithoutPrivIDsEtc"."contactPersonLender"))
44. 0.009 0.029 ↑ 50.0 2 1

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual time=0.010..0.029 rows=2 loops=1)

45. 0.020 0.020 ↓ 2.0 2 1

CTE Scan on "empWithoutPrivIDsEtc" (cost=0.00..0.02 rows=1 width=64) (actual time=0.003..0.020 rows=2 loops=1)

46. 0.008 0.023 ↓ 3.5 7 1

Hash (cost=1.02..1.02 rows=2 width=165) (actual time=0.022..0.023 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.015 0.015 ↓ 3.5 7 1

Seq Scan on "user" user_1 (cost=0.00..1.02 rows=2 width=165) (actual time=0.010..0.015 rows=7 loops=1)

48.          

CTE empCPs

49. 11.597 11.614 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.14..9.50 rows=1 width=173) (actual time=11.609..11.614 rows=1 loops=1)

  • Join Filter: (cp."userID" = us.id)
50. 0.016 0.016 ↑ 1.0 1 1

Index Scan using contact_person_owner_fkey_index on contact_person cp (cost=0.14..8.16 rows=1 width=137) (actual time=0.013..0.016 rows=1 loops=1)

  • Index Cond: (owner = 576481439)
  • Filter: (deleted IS NOT TRUE)
51. 0.001 0.001 ↑ 2.0 1 1

CTE Scan on "empOrigUserInv" us (cost=0.00..0.04 rows=2 width=132) (actual time=0.000..0.001 rows=1 loops=1)

52.          

CTE empWithoutLawAdditions

53. 4.547 16.499 ↓ 2.0 2 1

CTE Scan on "empWithoutPrivIDsEtc" "empWithoutPrivIDsEtc_1" (cost=0.00..18.77 rows=1 width=1,314) (actual time=16.386..16.499 rows=2 loops=1)

54.          

SubPlan (for CTE Scan)

55. 0.000 0.000 ↓ 0.0 0

Index Scan using private_site_pkey on private_site private_site_2 (cost=0.14..8.16 rows=1 width=1) (never executed)

  • Index Cond: (id = "empWithoutPrivIDsEtc_1"."simulatedBorrowerDepartment")
56. 0.018 0.018 ↓ 0.0 0 2

Seq Scan on site (cost=0.00..1.01 rows=1 width=1) (actual time=0.009..0.009 rows=0 loops=2)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."departmentBorrower")
  • Rows Removed by Filter: 6
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on site site_1 (cost=0.00..1.01 rows=1 width=1) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."departmentLender")
58. 0.000 0.000 ↓ 0.0 0

Seq Scan on site site_2 (cost=0.00..1.01 rows=1 width=1) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."departmentLender")
59. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_5" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."simulatedBorrower")
60. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_6" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."borrowerPrivID")
61. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_7" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."lenderPrivID")
62. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_8" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."lenderPrivID")
63. 0.004 0.004 ↑ 1.0 1 2

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_9" (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=2)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."lenderPrivID")
64. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_10" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."simulatedBorrowerDepartment")
65. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_11" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."borrowerPrivDepartmentID")
66. 0.000 0.000 ↓ 0.0 0

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_12" (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."lenderPrivDepartmentID")
67. 0.002 0.002 ↓ 0.0 0 2

CTE Scan on "privateSiteDataInvoled" "privateSiteDataInvoled_13" (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: (id = "empWithoutPrivIDsEtc_1"."lenderPrivDepartmentID")
  • Rows Removed by Filter: 1
68. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.04..0.05 rows=1 width=32) (never executed)

69. 0.000 0.000 ↓ 0.0 0

CTE Scan on "empCPs" (cost=0.00..0.03 rows=1 width=173) (never executed)

  • Filter: ("contactPersonID" = ANY ("empWithoutPrivIDsEtc_1"."simulatedBorrowerContactPerson"))
70. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=1.10..1.11 rows=1 width=32) (never executed)

71. 0.000 0.000 ↓ 0.0 0

CTE Scan on "empOrigUserInv" (cost=0.00..0.07 rows=2 width=133) (never executed)

  • Filter: (id = ANY ("empWithoutPrivIDsEtc_1"."contactPersonBorrower"))
72. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.04..0.05 rows=1 width=32) (never executed)

73. 0.000 0.000 ↓ 0.0 0

CTE Scan on "empCPs" "empCPs_1" (cost=0.00..0.03 rows=1 width=173) (never executed)

  • Filter: ("userID" = ANY ("empWithoutPrivIDsEtc_1"."contactPersonBorrower"))
74. 0.006 0.280 ↑ 1.0 1 2

Aggregate (cost=1.10..1.11 rows=1 width=32) (actual time=0.139..0.140 rows=1 loops=2)

75. 0.274 0.274 ↓ 0.0 0 2

CTE Scan on "empOrigUserInv" "empOrigUserInv_1" (cost=0.00..0.07 rows=2 width=133) (actual time=0.137..0.137 rows=0 loops=2)

  • Filter: (id = ANY ("empWithoutPrivIDsEtc_1"."contactPersonBorrower"))
  • Rows Removed by Filter: 1
76. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.59..0.60 rows=1 width=32) (never executed)

77. 0.000 0.000 ↓ 0.0 0

CTE Scan on "empOrigUserInv" "empOrigUserInv_2" (cost=0.00..0.07 rows=1 width=133) (never executed)

  • Filter: ((visib <> 'hidden'::text) AND (id = ANY ("empWithoutPrivIDsEtc_1"."contactPersonLender")))
78. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.04..0.05 rows=1 width=32) (never executed)

79. 0.000 0.000 ↓ 0.0 0

CTE Scan on "empCPs" "empCPs_2" (cost=0.00..0.03 rows=1 width=173) (never executed)

  • Filter: ("userID" = ANY ("empWithoutPrivIDsEtc_1"."contactPersonLender"))
80. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.59..0.60 rows=1 width=32) (never executed)

81. 0.000 0.000 ↓ 0.0 0

CTE Scan on "empOrigUserInv" "empOrigUserInv_3" (cost=0.00..0.07 rows=1 width=133) (never executed)

  • Filter: ((visib <> 'hidden'::text) AND (id = ANY ("empWithoutPrivIDsEtc_1"."contactPersonLender")))
82. 0.022 11.648 ↑ 1.0 1 2

Aggregate (cost=0.04..0.05 rows=1 width=32) (actual time=5.824..5.824 rows=1 loops=2)

83. 11.626 11.626 ↑ 1.0 1 2

CTE Scan on "empCPs" "empCPs_3" (cost=0.00..0.03 rows=1 width=173) (actual time=5.810..5.813 rows=1 loops=2)

  • Filter: ("userID" = ANY ("empWithoutPrivIDsEtc_1"."contactPersonLender"))
84.          

CTE empWithPreviousEmpsProcessed

85. 16.529 22.531 ↓ 2.0 2 1

CTE Scan on "empWithoutLawAdditions" (cost=0.00..12.78 rows=1 width=1,346) (actual time=20.581..22.531 rows=2 loops=1)

86.          

SubPlan (for CTE Scan)

87. 0.030 6.002 ↑ 1.0 1 2

Aggregate (cost=12.75..12.76 rows=1 width=32) (actual time=3.001..3.001 rows=1 loops=2)

88. 5.972 5.972 ↑ 1,000.0 1 2

Function Scan on german_law_transfer_duration_employments_processed f (cost=0.25..10.25 rows=1,000 width=40) (actual time=2.986..2.986 rows=1 loops=2)

89.          

CTE empLawPrep

90. 24.779 24.779 ↓ 2.0 2 1

CTE Scan on "empWithPreviousEmpsProcessed" (cost=0.00..0.54 rows=1 width=1,386) (actual time=22.713..24.779 rows=2 loops=1)

Planning time : 8.352 ms
Execution time : 26.855 ms