explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jgsA

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,066.547 ↓ 8.0 8 1

Limit (cost=90,056.85..90,056.85 rows=1 width=105) (actual time=1,066.546..1,066.547 rows=8 loops=1)

2.          

CTE not_active_nodes

3. 0.518 0.518 ↑ 1.0 27 1

Seq Scan on certnames certnames_1 (cost=0.00..87.09 rows=27 width=27) (actual time=0.005..0.518 rows=27 loops=1)

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 2,682
4. 0.058 1,066.544 ↓ 8.0 8 1

Sort (cost=89,969.76..89,969.76 rows=1 width=105) (actual time=1,066.543..1,066.544 rows=8 loops=1)

  • Sort Key: reports.end_time DESC
  • Sort Method: quicksort Memory: 26kB
5. 0.038 1,066.486 ↓ 8.0 8 1

Nested Loop Semi Join (cost=112.50..89,969.75 rows=1 width=105) (actual time=1,014.002..1,066.486 rows=8 loops=1)

  • Join Filter: (certnames.certname = factsets_1.certname)
6. 4.412 1,058.208 ↓ 3.3 20 1

Nested Loop Semi Join (cost=111.81..89,912.68 rows=6 width=121) (actual time=1,012.577..1,058.208 rows=20 loops=1)

  • Join Filter: (certnames.certname = factsets.certname)
  • Rows Removed by Join Filter: 56,093
7. 1.019 42.682 ↓ 2.0 2,682 1

Nested Loop Left Join (cost=111.39..89,088.27 rows=1,354 width=94) (actual time=2.285..42.682 rows=2,682 loops=1)

  • Join Filter: (reports_environment.id = reports.environment_id)
  • Rows Removed by Join Filter: 4,675
8. 1.138 41.663 ↓ 2.0 2,682 1

Nested Loop Left Join (cost=111.39..88,999.21 rows=1,354 width=90) (actual time=2.268..41.663 rows=2,682 loops=1)

  • Join Filter: (reports.status_id = report_statuses.id)
  • Rows Removed by Join Filter: 5,268
9. 1.558 40.525 ↓ 2.0 2,682 1

Nested Loop Left Join (cost=111.39..88,947.39 rows=1,354 width=90) (actual time=2.255..40.525 rows=2,682 loops=1)

10. 1.164 4.101 ↓ 2.0 2,682 1

Hash Right Join (cost=111.39..319.61 rows=1,354 width=35) (actual time=1.609..4.101 rows=2,682 loops=1)

  • Hash Cond: (catalogs.certname = certnames.certname)
11. 1.365 1.365 ↑ 1.0 2,709 1

Seq Scan on catalogs (cost=0.00..201.09 rows=2,709 width=35) (actual time=0.018..1.365 rows=2,709 loops=1)

12. 0.485 1.572 ↓ 2.0 2,682 1

Hash (cost=94.47..94.47 rows=1,354 width=35) (actual time=1.572..1.572 rows=2,682 loops=1)

  • Buckets: 4,096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 224kB
13. 0.560 1.087 ↓ 2.0 2,682 1

Seq Scan on certnames (cost=0.61..94.47 rows=1,354 width=35) (actual time=0.567..1.087 rows=2,682 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 27
14.          

SubPlan (for Seq Scan)

15. 0.527 0.527 ↑ 1.0 27 1

CTE Scan on not_active_nodes (cost=0.00..0.54 rows=27 width=32) (actual time=0.009..0.527 rows=27 loops=1)

16. 5.364 34.866 ↑ 15.0 1 2,682

Append (cost=0.00..65.31 rows=15 width=98) (actual time=0.013..0.013 rows=1 loops=2,682)

17. 0.000 0.000 ↓ 0.0 0 2,682

Seq Scan on reports (cost=0.00..0.00 rows=1 width=131) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Filter: ((certnames.certname = certname) AND (certnames.latest_report_id = id))
18. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200723z on reports_20200723z (cost=0.29..4.69 rows=1 width=88) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
19. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200724z on reports_20200724z (cost=0.29..4.67 rows=1 width=115) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
20. 0.000 0.000 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200725z on reports_20200725z (cost=0.29..4.67 rows=1 width=115) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
21. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200726z on reports_20200726z (cost=0.29..4.66 rows=1 width=115) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
22. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200727z on reports_20200727z (cost=0.29..4.63 rows=1 width=88) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
23. 0.000 0.000 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200728z on reports_20200728z (cost=0.29..4.68 rows=1 width=88) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
24. 0.000 0.000 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200729z on reports_20200729z (cost=0.29..4.68 rows=1 width=88) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
25. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200730z on reports_20200730z (cost=0.29..4.69 rows=1 width=88) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
26. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200718z on reports_20200718z (cost=0.29..4.67 rows=1 width=115) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
27. 0.000 0.000 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200719z on reports_20200719z (cost=0.29..4.67 rows=1 width=115) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
28. 0.000 0.000 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200720z on reports_20200720z (cost=0.29..4.67 rows=1 width=88) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
29. 0.000 0.000 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200721z on reports_20200721z (cost=0.29..4.68 rows=1 width=88) (actual time=0.000..0.000 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
30. 2.682 2.682 ↓ 0.0 0 2,682

Index Scan using idx_reports_id_20200722z on reports_20200722z (cost=0.29..4.68 rows=1 width=88) (actual time=0.001..0.001 rows=0 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
31. 10.728 10.728 ↑ 1.0 1 2,682

Index Scan using idx_reports_id_20200731z on reports_20200731z (cost=0.29..4.48 rows=1 width=88) (actual time=0.003..0.004 rows=1 loops=2,682)

  • Index Cond: (certnames.latest_report_id = id)
  • Filter: (certnames.certname = certname)
32. 0.000 0.000 ↑ 1.0 3 2,682

Materialize (cost=0.00..1.04 rows=3 width=16) (actual time=0.000..0.000 rows=3 loops=2,682)

33. 0.007 0.007 ↑ 1.0 3 1

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

34. 0.000 0.000 ↑ 1.7 3 2,682

Materialize (cost=0.00..1.07 rows=5 width=20) (actual time=0.000..0.000 rows=3 loops=2,682)

35. 0.009 0.009 ↑ 1.2 4 1

Seq Scan on environments reports_environment (cost=0.00..1.05 rows=5 width=20) (actual time=0.009..0.009 rows=4 loops=1)

36. 1.213 1,011.114 ↓ 1.6 21 2,682

Materialize (cost=0.41..560.41 rows=13 width=27) (actual time=0.010..0.377 rows=21 loops=2,682)

37. 1,006.951 1,009.901 ↓ 1.6 21 1

Nested Loop (cost=0.41..560.35 rows=13 width=27) (actual time=26.627..1,009.901 rows=21 loops=1)

  • Join Filter: ((jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array) IS NOT NULL) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array)) <> 'object'::text) AND ((jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths.path_array) #>> '{}'::text[]) ~ 'jws'::text))
  • Rows Removed by Join Filter: 2,688
38. 0.001 0.036 ↑ 1.0 1 1

Unique (cost=0.41..8.43 rows=1 width=686) (actual time=0.034..0.036 rows=1 loops=1)

39. 0.035 0.035 ↑ 1.0 1 1

Index Scan using fact_paths_path_type_unique on fact_paths (cost=0.41..8.43 rows=1 width=686) (actual time=0.034..0.035 rows=1 loops=1)

  • Index Cond: (path = 'approle'::text)
40. 2.914 2.914 ↑ 1.0 2,709 1

Seq Scan on factsets (cost=0.00..457.09 rows=2,709 width=298) (actual time=0.023..2.914 rows=2,709 loops=1)

41. 7.900 8.240 ↓ 0.0 0 20

Nested Loop (cost=0.70..9.50 rows=1 width=27) (actual time=0.412..0.412 rows=0 loops=20)

  • 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) = '"cloud_accp"'::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: 1
42. 0.200 0.200 ↑ 1.0 1 20

Index Scan using factsets_certname_idx on factsets factsets_1 (cost=0.28..1.01 rows=1 width=298) (actual time=0.009..0.010 rows=1 loops=20)

  • Index Cond: (certname = factsets.certname)
43. 0.020 0.140 ↑ 1.0 1 20

Unique (cost=0.41..8.43 rows=1 width=686) (actual time=0.007..0.007 rows=1 loops=20)

44. 0.120 0.120 ↑ 1.0 1 20

Index Scan using fact_paths_path_type_unique on fact_paths fact_paths_1 (cost=0.41..8.43 rows=1 width=686) (actual time=0.006..0.006 rows=1 loops=20)

  • Index Cond: (path = 'appenv'::text)
Planning time : 18.414 ms
Execution time : 1,067.220 ms