explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fYL6

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 68,047.550 ↓ 50.0 50 1

Limit (cost=2,958,230.60..2,958,230.61 rows=1 width=113) (actual time=68,047.540..68,047.550 rows=50 loops=1)

2.          

CTE not_active_nodes

3. 6.531 6.531 ↑ 1.0 1,060 1

Seq Scan on certnames certnames_1 (cost=0.00..736.78 rows=1,060 width=29) (actual time=0.005..6.531 rows=1,060 loops=1)

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 21,318
4. 4.907 68,047.543 ↓ 50.0 50 1

Sort (cost=2,957,493.82..2,957,493.83 rows=1 width=113) (actual time=68,047.536..68,047.543 rows=50 loops=1)

  • Sort Key: reports.end_time DESC
  • Sort Method: top-N heapsort Memory: 50kB
5. 515.720 68,042.636 ↓ 1,341.0 1,341 1

Nested Loop (cost=49,005.43..2,957,493.81 rows=1 width=113) (actual time=21,727.580..68,042.636 rows=1,341 loops=1)

  • Join Filter: (certnames.certname = factsets_2.certname)
  • Rows Removed by Join Filter: 8,668,100
6. 10.249 55,679.212 ↓ 1,364.0 1,364 1

Hash Semi Join (cost=29,327.46..2,937,815.82 rows=1 width=160) (actual time=11,489.017..55,679.212 rows=1,364 loops=1)

  • Hash Cond: (certnames.certname = factsets_1.certname)
7. 10,238.328 50,610.549 ↓ 9,654.0 9,654 1

Nested Loop Semi Join (cost=10,041.08..2,918,529.44 rows=1 width=131) (actual time=6,430.542..50,610.549 rows=9,654 loops=1)

  • Join Filter: (certnames.certname = factsets.certname)
  • Rows Removed by Join Filter: 162,428,397
8. 9.032 27,751.965 ↓ 1.9 21,318 1

Nested Loop Left Join (cost=10,040.53..2,899,075.23 rows=11,189 width=102) (actual time=4,325.933..27,751.965 rows=21,318 loops=1)

  • Join Filter: (reports_environment.id = reports.environment_id)
  • Rows Removed by Join Filter: 48
9. 11.733 27,742.933 ↓ 1.9 21,318 1

Nested Loop Left Join (cost=10,040.53..2,897,697.85 rows=11,189 width=98) (actual time=4,325.918..27,742.933 rows=21,318 loops=1)

  • Join Filter: (reports.status_id = report_statuses.id)
  • Rows Removed by Join Filter: 2,679
10. 45.535 27,731.200 ↓ 1.9 21,318 1

Hash Left Join (cost=10,040.53..2,897,277.23 rows=11,189 width=98) (actual time=4,325.868..27,731.200 rows=21,318 loops=1)

  • Hash Cond: (certnames.certname = catalogs.certname)
11. 3,761.065 27,644.801 ↓ 1.9 21,318 1

Hash Right Join (cost=984.41..2,888,067.35 rows=11,189 width=98) (actual time=4,284.738..27,644.801 rows=21,318 loops=1)

  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
12. 896.695 23,864.160 ↑ 1.0 13,612,762 1

Append (cost=0.00..2,815,607.77 rows=13,614,318 width=106) (actual time=0.064..23,864.160 rows=13,612,762 loops=1)

13. 374.238 374.238 ↑ 1.0 133,963 1

Seq Scan on reports (cost=0.00..28,461.24 rows=134,424 width=116) (actual time=0.062..374.238 rows=133,963 loops=1)

14. 2,465.646 2,465.646 ↑ 1.0 992,050 1

Seq Scan on reports_20200717z (cost=0.00..200,888.97 rows=992,897 width=91) (actual time=0.045..2,465.646 rows=992,050 loops=1)

15. 2,089.391 2,089.391 ↑ 1.0 992,840 1

Seq Scan on reports_20200718z (cost=0.00..200,217.19 rows=993,719 width=91) (actual time=0.030..2,089.391 rows=992,840 loops=1)

16. 2,169.222 2,169.222 ↑ 1.0 995,549 1

Seq Scan on reports_20200719z (cost=0.00..200,597.99 rows=996,399 width=91) (actual time=0.025..2,169.222 rows=995,549 loops=1)

17. 1,901.301 1,901.301 ↑ 1.0 998,548 1

Seq Scan on reports_20200720z (cost=0.00..201,483.38 rows=998,838 width=117) (actual time=0.031..1,901.301 rows=998,548 loops=1)

18. 1,898.161 1,898.161 ↑ 1.0 1,000,727 1

Seq Scan on reports_20200721z (cost=0.00..201,718.99 rows=1,001,099 width=117) (actual time=0.035..1,898.161 rows=1,000,727 loops=1)

19. 2,407.105 2,407.105 ↑ 1.0 1,006,064 1

Seq Scan on reports_20200722z (cost=0.00..202,687.85 rows=1,007,085 width=91) (actual time=0.033..2,407.105 rows=1,006,064 loops=1)

20. 1,547.136 1,547.136 ↑ 1.0 1,004,280 1

Seq Scan on reports_20200723z (cost=0.00..202,267.57 rows=1,004,357 width=91) (actual time=0.023..1,547.136 rows=1,004,280 loops=1)

21. 1,025.910 1,025.910 ↓ 1.0 739,196 1

Seq Scan on reports_20200724z (cost=0.00..152,005.03 rows=738,103 width=91) (actual time=0.024..1,025.910 rows=739,196 loops=1)

22. 1,507.016 1,507.016 ↑ 1.0 1,003,110 1

Seq Scan on reports_20200725z (cost=0.00..201,535.49 rows=1,003,149 width=117) (actual time=0.031..1,507.016 rows=1,003,110 loops=1)

23. 1,315.114 1,315.114 ↑ 1.0 999,224 1

Seq Scan on reports_20200726z (cost=0.00..200,990.34 rows=999,534 width=117) (actual time=0.021..1,315.114 rows=999,224 loops=1)

24. 1,254.448 1,254.448 ↓ 1.0 1,006,664 1

Seq Scan on reports_20200727z (cost=0.00..202,568.20 rows=1,006,320 width=117) (actual time=0.026..1,254.448 rows=1,006,664 loops=1)

25. 1,111.755 1,111.755 ↓ 1.0 1,008,452 1

Seq Scan on reports_20200728z (cost=0.00..203,135.76 rows=1,007,176 width=117) (actual time=0.025..1,111.755 rows=1,008,452 loops=1)

26. 1,128.911 1,128.911 ↓ 1.0 1,009,059 1

Seq Scan on reports_20200729z (cost=0.00..203,166.74 rows=1,008,774 width=117) (actual time=0.033..1,128.911 rows=1,009,059 loops=1)

27. 772.111 772.111 ↓ 1.0 723,036 1

Seq Scan on reports_20200730z (cost=0.00..145,811.44 rows=722,444 width=117) (actual time=0.024..772.111 rows=723,036 loops=1)

28. 5.589 19.576 ↓ 1.9 21,318 1

Hash (cost=816.58..816.58 rows=11,189 width=37) (actual time=19.575..19.576 rows=21,318 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,772kB
29. 7.167 13.987 ↓ 1.9 21,318 1

Seq Scan on certnames (cost=23.85..816.58 rows=11,189 width=37) (actual time=7.193..13.987 rows=21,318 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 1,060
30.          

SubPlan (for Seq Scan)

31. 6.820 6.820 ↑ 1.0 1,060 1

CTE Scan on not_active_nodes (cost=0.00..21.20 rows=1,060 width=32) (actual time=0.009..6.820 rows=1,060 loops=1)

32. 5.693 40.864 ↑ 1.0 22,361 1

Hash (cost=8,776.61..8,776.61 rows=22,361 width=37) (actual time=40.864..40.864 rows=22,361 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,847kB
33. 35.171 35.171 ↑ 1.0 22,361 1

Seq Scan on catalogs (cost=0.00..8,776.61 rows=22,361 width=37) (actual time=0.071..35.171 rows=22,361 loops=1)

34. 0.000 0.000 ↑ 3.0 1 21,318

Materialize (cost=0.00..1.04 rows=3 width=16) (actual time=0.000..0.000 rows=1 loops=21,318)

35. 0.028 0.028 ↑ 1.0 3 1

Seq Scan on report_statuses (cost=0.00..1.03 rows=3 width=16) (actual time=0.024..0.028 rows=3 loops=1)

36. 0.000 0.000 ↑ 9.0 1 21,318

Materialize (cost=0.00..1.14 rows=9 width=20) (actual time=0.000..0.000 rows=1 loops=21,318)

37. 0.018 0.018 ↑ 1.5 6 1

Seq Scan on environments reports_environment (cost=0.00..1.09 rows=9 width=20) (actual time=0.007..0.018 rows=6 loops=1)

38. 6,253.157 12,620.256 ↓ 7,620.0 7,620 21,318

Materialize (cost=0.55..19,286.37 rows=1 width=29) (actual time=0.000..0.592 rows=7,620 loops=21,318)

39. 6,280.855 6,367.099 ↓ 9,813.0 9,813 1

Nested Loop (cost=0.55..19,286.37 rows=1 width=29) (actual time=1.451..6,367.099 rows=9,813 loops=1)

  • Join Filter: ((jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array) IS NOT NULL) AND (jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array) = '"us.deloitte.com"'::jsonb) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array)) <> 'object'::text) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array)) = 'string'::text))
  • Rows Removed by Join Filter: 12,564
40. 0.008 0.094 ↑ 1.0 1 1

Unique (cost=0.55..8.56 rows=1 width=767) (actual time=0.082..0.094 rows=1 loops=1)

41. 0.086 0.086 ↑ 1.0 1 1

Index Scan using fact_paths_path_type_unique on fact_paths (cost=0.55..8.56 rows=1 width=767) (actual time=0.080..0.086 rows=1 loops=1)

  • Index Cond: (path = 'domain'::text)
42. 86.150 86.150 ↑ 1.0 22,377 1

Seq Scan on factsets (cost=0.00..18,326.77 rows=22,377 width=492) (actual time=0.016..86.150 rows=22,377 loops=1)

43. 2.206 5,058.414 ↓ 2,211.0 2,211 1

Hash (cost=19,286.37..19,286.37 rows=1 width=29) (actual time=5,058.414..5,058.414 rows=2,211 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 177kB
44. 5,004.335 5,056.208 ↓ 2,211.0 2,211 1

Nested Loop (cost=0.55..19,286.37 rows=1 width=29) (actual time=1.204..5,056.208 rows=2,211 loops=1)

  • Join Filter: ((jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array) IS NOT NULL) AND (jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array) = '"azure"'::jsonb) AND (jsonb_typeof(jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array)) <> 'object'::text) AND (jsonb_typeof(jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array)) = 'string'::text))
  • Rows Removed by Join Filter: 20,166
45. 0.003 0.060 ↑ 1.0 1 1

Unique (cost=0.55..8.56 rows=1 width=767) (actual time=0.047..0.060 rows=1 loops=1)

46. 0.057 0.057 ↑ 1.0 1 1

Index Scan using fact_paths_path_type_unique on fact_paths fact_paths_1 (cost=0.55..8.56 rows=1 width=767) (actual time=0.044..0.057 rows=1 loops=1)

  • Index Cond: (path = 'deloitte_hostingprovider'::text)
47. 51.813 51.813 ↑ 1.0 22,377 1

Seq Scan on factsets factsets_1 (cost=0.00..18,326.77 rows=22,377 width=492) (actual time=0.015..51.813 rows=22,377 loops=1)

48. 1,626.079 11,847.704 ↓ 6,356.0 6,356 1,364

HashAggregate (cost=19,677.97..19,677.98 rows=1 width=29) (actual time=7.505..8.686 rows=6,356 loops=1,364)

  • Group Key: factsets_2.certname
49. 10,186.180 10,221.625 ↓ 12,419.0 12,419 1

Nested Loop (cost=0.55..19,677.97 rows=1 width=29) (actual time=0.516..10,221.625 rows=12,419 loops=1)

  • Join Filter: ((jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_2.path_array) IS NOT NULL) AND (jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_2.path_array)) <> 'object'::text) AND (((jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_2.path_array)) = 'boolean'::text) AND (jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_2.path_array) = 'true'::jsonb)) OR ((jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_2.path_array)) = 'string'::text) AND (jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_2.path_array) = '"true"'::jsonb))))
  • Rows Removed by Join Filter: 9,958
50. 0.003 0.059 ↑ 1.0 1 1

Unique (cost=0.55..8.56 rows=1 width=767) (actual time=0.047..0.059 rows=1 loops=1)

51. 0.056 0.056 ↑ 1.0 1 1

Index Scan using fact_paths_path_type_unique on fact_paths fact_paths_2 (cost=0.55..8.56 rows=1 width=767) (actual time=0.044..0.056 rows=1 loops=1)

  • Index Cond: (path = 'deloitte_hardened'::text)
52. 35.386 35.386 ↑ 1.0 22,377 1

Seq Scan on factsets factsets_2 (cost=0.00..18,326.77 rows=22,377 width=492) (actual time=0.014..35.386 rows=22,377 loops=1)

Planning time : 18.262 ms
Execution time : 68,049.009 ms