explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cG4m

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2.326 ↓ 0.0 0 1

Limit (cost=278.05..278.05 rows=1 width=131) (actual time=2.326..2.326 rows=0 loops=1)

2.          

CTE not_active_nodes

3. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on certnames certnames_1 (cost=0.00..1.03 rows=1 width=34) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 3
4. 0.012 2.325 ↓ 0.0 0 1

Sort (cost=277.02..277.02 rows=1 width=131) (actual time=2.325..2.325 rows=0 loops=1)

  • Sort Key: reports.end_time DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.001 2.313 ↓ 0.0 0 1

Nested Loop Semi Join (cost=2.18..277.01 rows=1 width=131) (actual time=2.313..2.313 rows=0 loops=1)

  • Join Filter: (certnames.certname = factsets.certname)
6. 0.000 2.312 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1.77..266.78 rows=1 width=193) (actual time=2.312..2.312 rows=0 loops=1)

  • Join Filter: (certnames.certname = factsets_2.certname)
7. 0.001 2.312 ↓ 0.0 0 1

Nested Loop Semi Join (cost=1.36..256.54 rows=1 width=159) (actual time=2.312..2.312 rows=0 loops=1)

  • Join Filter: (certnames.certname = factsets_1.certname)
8. 0.004 2.287 ↓ 1.5 3 1

Nested Loop Left Join (cost=1.09..240.10 rows=2 width=125) (actual time=1.836..2.287 rows=3 loops=1)

  • Join Filter: (reports_environment.id = reports.environment_id)
9. 0.003 2.280 ↓ 1.5 3 1

Nested Loop Left Join (cost=1.09..239.03 rows=2 width=119) (actual time=1.831..2.280 rows=3 loops=1)

  • Join Filter: (reports.status_id = report_statuses.id)
  • Rows Removed by Join Filter: 1
10. 0.005 2.256 ↓ 1.5 3 1

Nested Loop Left Join (cost=1.09..237.92 rows=2 width=119) (actual time=1.810..2.256 rows=3 loops=1)

  • Join Filter: (catalogs.certname = certnames.certname)
  • Rows Removed by Join Filter: 6
11. 0.216 2.239 ↓ 1.5 3 1

Hash Right Join (cost=1.09..236.79 rows=2 width=119) (actual time=1.800..2.239 rows=3 loops=1)

  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
12. 0.109 1.988 ↑ 1.5 941 1

Append (cost=0.00..228.26 rows=1,418 width=126) (actual time=0.018..1.988 rows=941 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on reports (cost=0.00..0.00 rows=1 width=131) (actual time=0.002..0.002 rows=0 loops=1)

14. 0.326 0.326 ↓ 1.0 144 1

Seq Scan on reports_20200715z (cost=0.00..33.40 rows=140 width=122) (actual time=0.015..0.326 rows=144 loops=1)

15. 0.172 0.172 ↓ 1.0 96 1

Seq Scan on reports_20200716z (cost=0.00..20.93 rows=93 width=121) (actual time=0.010..0.172 rows=96 loops=1)

16. 0.196 0.196 ↓ 1.0 101 1

Seq Scan on reports_20200717z (cost=0.00..21.97 rows=97 width=122) (actual time=0.010..0.196 rows=101 loops=1)

17. 0.264 0.264 ↓ 1.1 137 1

Seq Scan on reports_20200718z (cost=0.00..29.27 rows=127 width=121) (actual time=0.011..0.264 rows=137 loops=1)

18. 0.221 0.221 ↓ 1.0 96 1

Seq Scan on reports_20200719z (cost=0.00..20.93 rows=93 width=121) (actual time=0.009..0.221 rows=96 loops=1)

19. 0.165 0.165 ↓ 1.0 96 1

Seq Scan on reports_20200720z (cost=0.00..20.93 rows=93 width=121) (actual time=0.010..0.165 rows=96 loops=1)

20. 0.160 0.160 ↓ 1.0 76 1

Seq Scan on reports_20200721z (cost=0.00..16.74 rows=74 width=121) (actual time=0.014..0.160 rows=76 loops=1)

21. 0.041 0.041 ↑ 8.8 16 1

Seq Scan on reports_20200724z (cost=0.00..11.40 rows=140 width=131) (actual time=0.015..0.041 rows=16 loops=1)

22. 0.082 0.082 ↑ 2.9 48 1

Seq Scan on reports_20200725z (cost=0.00..11.40 rows=140 width=131) (actual time=0.009..0.082 rows=48 loops=1)

23. 0.100 0.100 ↑ 2.9 48 1

Seq Scan on reports_20200726z (cost=0.00..11.40 rows=140 width=131) (actual time=0.017..0.100 rows=48 loops=1)

24. 0.096 0.096 ↑ 2.9 49 1

Seq Scan on reports_20200727z (cost=0.00..11.40 rows=140 width=131) (actual time=0.017..0.096 rows=49 loops=1)

25. 0.054 0.054 ↑ 4.1 34 1

Seq Scan on reports_20200728z (cost=0.00..11.40 rows=140 width=131) (actual time=0.009..0.054 rows=34 loops=1)

26. 0.010 0.035 ↓ 1.5 3 1

Hash (cost=1.06..1.06 rows=2 width=42) (actual time=0.035..0.035 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.019 0.025 ↓ 1.5 3 1

Seq Scan on certnames (cost=0.02..1.06 rows=2 width=42) (actual time=0.024..0.025 rows=3 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
28.          

SubPlan (for Seq Scan)

29. 0.006 0.006 ↓ 0.0 0 1

CTE Scan on not_active_nodes (cost=0.00..0.02 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=1)

30. 0.006 0.012 ↑ 1.0 3 3

Materialize (cost=0.00..1.04 rows=3 width=42) (actual time=0.003..0.004 rows=3 loops=3)

31. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on catalogs (cost=0.00..1.03 rows=3 width=42) (actual time=0.005..0.006 rows=3 loops=1)

32. 0.003 0.021 ↑ 3.0 1 3

Materialize (cost=0.00..1.04 rows=3 width=16) (actual time=0.006..0.007 rows=1 loops=3)

33. 0.018 0.018 ↑ 1.5 2 1

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

34. 0.000 0.003 ↑ 2.0 1 3

Materialize (cost=0.00..1.03 rows=2 width=22) (actual time=0.001..0.001 rows=1 loops=3)

35. 0.003 0.003 ↑ 2.0 1 1

Seq Scan on environments reports_environment (cost=0.00..1.02 rows=2 width=22) (actual time=0.003..0.003 rows=1 loops=1)

36. 0.000 0.024 ↓ 0.0 0 3

Materialize (cost=0.27..16.41 rows=1 width=34) (actual time=0.008..0.008 rows=0 loops=3)

37. 0.001 0.024 ↓ 0.0 0 1

Nested Loop (cost=0.27..16.40 rows=1 width=34) (actual time=0.023..0.024 rows=0 loops=1)

  • Join Filter: ((jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths.path_array) IS NOT NULL) AND (jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths.path_array) = '"azure"'::jsonb) AND (jsonb_typeof(jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths.path_array)) <> 'object'::text) AND (jsonb_typeof(jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths.path_array)) = 'string'::text))
38. 0.000 0.023 ↓ 0.0 0 1

Unique (cost=0.27..8.29 rows=1 width=598) (actual time=0.023..0.023 rows=0 loops=1)

39. 0.023 0.023 ↓ 0.0 0 1

Index Scan using fact_paths_path_type_unique on fact_paths (cost=0.27..8.29 rows=1 width=598) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: (path = 'deloitte_hostingprovider'::text)
40. 0.000 0.000 ↓ 0.0 0

Seq Scan on factsets factsets_1 (cost=0.00..7.21 rows=21 width=1,096) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.41..10.23 rows=1 width=34) (never executed)

  • Join Filter: ((jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_1.path_array) IS NOT NULL) AND (jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_1.path_array)) <> 'object'::text) AND (((jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_1.path_array)) = 'boolean'::text) AND (jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_1.path_array) = 'true'::jsonb)) OR ((jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_1.path_array)) = 'string'::text) AND (jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths_1.path_array) = '"true"'::jsonb))))
42. 0.000 0.000 ↓ 0.0 0

Index Scan using factsets_certname_idx on factsets factsets_2 (cost=0.14..1.87 rows=1 width=1,096) (never executed)

  • Index Cond: (certname = factsets_1.certname)
43. 0.000 0.000 ↓ 0.0 0

Unique (cost=0.27..8.29 rows=1 width=598) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using fact_paths_path_type_unique on fact_paths fact_paths_1 (cost=0.27..8.29 rows=1 width=598) (never executed)

  • Index Cond: (path = 'deloitte_hardened'::text)
45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.41..10.21 rows=1 width=34) (never executed)

  • Join Filter: ((jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_2.path_array) IS NOT NULL) AND (jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_2.path_array) = '"us.deloitte.com"'::jsonb) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_2.path_array)) <> 'object'::text) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_2.path_array)) = 'string'::text))
46. 0.000 0.000 ↓ 0.0 0

Index Scan using factsets_certname_idx on factsets (cost=0.14..1.87 rows=1 width=1,096) (never executed)

  • Index Cond: (certname = factsets_1.certname)
47. 0.000 0.000 ↓ 0.0 0

Unique (cost=0.27..8.29 rows=1 width=598) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using fact_paths_path_type_unique on fact_paths fact_paths_2 (cost=0.27..8.29 rows=1 width=598) (never executed)

  • Index Cond: (path = 'domain'::text)