explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T9KV

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7.343 ↓ 3.0 3 1

Limit (cost=113.42..113.42 rows=1 width=131) (actual time=7.343..7.343 rows=3 loops=1)

  • Output: certnames.certname, reports_environment.environment, reports.end_time, (encode(reports.hash, 'hex'::text)), report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id
2.          

CTE not_active_nodes

3. 0.003 0.003 ↓ 0.0 0 1

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

  • Output: certnames_1.certname
  • Filter: ((certnames_1.deactivated IS NOT NULL) OR (certnames_1.expired IS NOT NULL))
  • Rows Removed by Filter: 3
4. 0.027 7.341 ↓ 3.0 3 1

Sort (cost=112.39..112.39 rows=1 width=131) (actual time=7.341..7.341 rows=3 loops=1)

  • Output: certnames.certname, reports_environment.environment, reports.end_time, (encode(reports.hash, 'hex'::text)), report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id
  • Sort Key: reports.end_time DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.027 7.314 ↓ 3.0 3 1

Nested Loop Semi Join (cost=93.40..112.38 rows=1 width=131) (actual time=4.965..7.314 rows=3 loops=1)

  • Output: certnames.certname, reports_environment.environment, reports.end_time, encode(reports.hash, 'hex'::text), report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id
  • Join Filter: (certnames.certname = factsets.certname)
  • Rows Removed by Join Filter: 3
6. 0.006 2.013 ↓ 3.0 3 1

Nested Loop Semi Join (cost=93.13..102.80 rows=1 width=163) (actual time=1.985..2.013 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, report_statuses.status, reports_environment.environment, factsets_1.certname
  • Join Filter: (certnames.certname = factsets_1.certname)
  • Rows Removed by Join Filter: 3
7. 0.002 0.444 ↓ 1.5 3 1

Nested Loop Left Join (cost=92.86..93.31 rows=2 width=129) (actual time=0.419..0.444 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, report_statuses.status, reports_environment.environment
  • Inner Unique: true
8. 0.008 0.436 ↓ 1.5 3 1

Nested Loop Left Join (cost=92.73..92.98 rows=2 width=123) (actual time=0.416..0.436 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.environment_id, report_statuses.status
  • Inner Unique: true
9. 0.012 0.419 ↓ 1.5 3 1

Merge Left Join (cost=92.60..92.65 rows=2 width=123) (actual time=0.409..0.419 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.status_id, reports.environment_id
  • Merge Cond: (certnames.certname = catalogs.certname)
10. 0.012 0.398 ↓ 1.5 3 1

Sort (cost=91.55..91.55 rows=2 width=123) (actual time=0.398..0.398 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.status_id, reports.environment_id
  • Sort Key: certnames.certname
  • Sort Method: quicksort Memory: 25kB
11. 0.045 0.386 ↓ 1.5 3 1

Hash Right Join (cost=1.09..91.54 rows=2 width=123) (actual time=0.378..0.386 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.status_id, reports.environment_id
  • Inner Unique: true
  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
12. 0.023 0.317 ↑ 1.4 329 1

Append (cost=0.00..88.00 rows=467 width=129) (actual time=0.006..0.317 rows=329 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

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

  • Output: reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.certname, reports.id, reports.status_id, reports.environment_id
14. 0.066 0.066 ↑ 1.0 76 1

Seq Scan on public.reports_20200721z (cost=0.00..16.76 rows=76 width=121) (actual time=0.004..0.066 rows=76 loops=1)

  • Output: reports_20200721z.end_time, reports_20200721z.hash, reports_20200721z.noop, reports_20200721z.noop_pending, reports_20200721z.corrective_change, reports_20200721z.job_id, reports_20200721z.certname, reports_20200721z.id, reports_20200721z.status_id, reports_20200721z.environment_id
15. 0.014 0.014 ↑ 1.0 16 1

Seq Scan on public.reports_20200724z (cost=0.00..4.16 rows=16 width=131) (actual time=0.003..0.014 rows=16 loops=1)

  • Output: reports_20200724z.end_time, reports_20200724z.hash, reports_20200724z.noop, reports_20200724z.noop_pending, reports_20200724z.corrective_change, reports_20200724z.job_id, reports_20200724z.certname, reports_20200724z.id, reports_20200724z.status_id, reports_20200724z.environment_id
16. 0.041 0.041 ↑ 1.0 48 1

Seq Scan on public.reports_20200725z (cost=0.00..10.48 rows=48 width=131) (actual time=0.003..0.041 rows=48 loops=1)

  • Output: reports_20200725z.end_time, reports_20200725z.hash, reports_20200725z.noop, reports_20200725z.noop_pending, reports_20200725z.corrective_change, reports_20200725z.job_id, reports_20200725z.certname, reports_20200725z.id, reports_20200725z.status_id, reports_20200725z.environment_id
17. 0.038 0.038 ↑ 1.0 48 1

Seq Scan on public.reports_20200726z (cost=0.00..10.48 rows=48 width=131) (actual time=0.005..0.038 rows=48 loops=1)

  • Output: reports_20200726z.end_time, reports_20200726z.hash, reports_20200726z.noop, reports_20200726z.noop_pending, reports_20200726z.corrective_change, reports_20200726z.job_id, reports_20200726z.certname, reports_20200726z.id, reports_20200726z.status_id, reports_20200726z.environment_id
18. 0.042 0.042 ↑ 1.0 49 1

Seq Scan on public.reports_20200727z (cost=0.00..10.49 rows=49 width=131) (actual time=0.004..0.042 rows=49 loops=1)

  • Output: reports_20200727z.end_time, reports_20200727z.hash, reports_20200727z.noop, reports_20200727z.noop_pending, reports_20200727z.corrective_change, reports_20200727z.job_id, reports_20200727z.certname, reports_20200727z.id, reports_20200727z.status_id, reports_20200727z.environment_id
19. 0.037 0.037 ↑ 1.0 38 1

Seq Scan on public.reports_20200728z (cost=0.00..8.38 rows=38 width=131) (actual time=0.003..0.037 rows=38 loops=1)

  • Output: reports_20200728z.end_time, reports_20200728z.hash, reports_20200728z.noop, reports_20200728z.noop_pending, reports_20200728z.corrective_change, reports_20200728z.job_id, reports_20200728z.certname, reports_20200728z.id, reports_20200728z.status_id, reports_20200728z.environment_id
20. 0.011 0.011 ↑ 1.0 11 1

Seq Scan on public.reports_20200729z (cost=0.00..3.11 rows=11 width=131) (actual time=0.004..0.011 rows=11 loops=1)

  • Output: reports_20200729z.end_time, reports_20200729z.hash, reports_20200729z.noop, reports_20200729z.noop_pending, reports_20200729z.corrective_change, reports_20200729z.job_id, reports_20200729z.certname, reports_20200729z.id, reports_20200729z.status_id, reports_20200729z.environment_id
21. 0.039 0.039 ↓ 1.0 41 1

Seq Scan on public.reports_20200730z (cost=0.00..10.40 rows=40 width=131) (actual time=0.003..0.039 rows=41 loops=1)

  • Output: reports_20200730z.end_time, reports_20200730z.hash, reports_20200730z.noop, reports_20200730z.noop_pending, reports_20200730z.corrective_change, reports_20200730z.job_id, reports_20200730z.certname, reports_20200730z.id, reports_20200730z.status_id, reports_20200730z.environment_id
22. 0.004 0.004 ↑ 70.0 2 1

Seq Scan on public.reports_20200803z (cost=0.00..11.40 rows=140 width=131) (actual time=0.003..0.004 rows=2 loops=1)

  • Output: reports_20200803z.end_time, reports_20200803z.hash, reports_20200803z.noop, reports_20200803z.noop_pending, reports_20200803z.corrective_change, reports_20200803z.job_id, reports_20200803z.certname, reports_20200803z.id, reports_20200803z.status_id, reports_20200803z.environment_id
23. 0.004 0.024 ↓ 1.5 3 1

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

  • Output: certnames.certname, certnames.latest_report_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.016 0.020 ↓ 1.5 3 1

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

  • Output: certnames.certname, certnames.latest_report_id
  • Filter: (NOT (hashed SubPlan 2))
25.          

SubPlan (for Seq Scan)

26. 0.004 0.004 ↓ 0.0 0 1

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

  • Output: not_active_nodes.certname
27. 0.006 0.009 ↑ 1.0 3 1

Sort (cost=1.05..1.06 rows=3 width=42) (actual time=0.008..0.009 rows=3 loops=1)

  • Output: catalogs.certname, catalogs.environment_id
  • Sort Key: catalogs.certname
  • Sort Method: quicksort Memory: 25kB
28. 0.003 0.003 ↑ 1.0 3 1

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

  • Output: catalogs.certname, catalogs.environment_id
29. 0.009 0.009 ↑ 1.0 1 3

Index Scan using report_statuses_pkey on public.report_statuses (cost=0.13..0.17 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3)

  • Output: report_statuses.id, report_statuses.status
  • Index Cond: (reports.status_id = report_statuses.id)
30. 0.006 0.006 ↑ 1.0 1 3

Index Scan using environments_pkey on public.environments reports_environment (cost=0.13..0.17 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=3)

  • Output: reports_environment.id, reports_environment.environment
  • Index Cond: (reports_environment.id = reports.environment_id)
31. 0.002 1.563 ↓ 2.0 2 3

Materialize (cost=0.27..9.46 rows=1 width=34) (actual time=0.194..0.521 rows=2 loops=3)

  • Output: factsets_1.certname
32. 1.537 1.561 ↓ 3.0 3 1

Nested Loop (cost=0.27..9.46 rows=1 width=34) (actual time=0.581..1.561 rows=3 loops=1)

  • Output: factsets_1.certname
  • 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) = '"CentOS"'::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))
33. 0.001 0.021 ↑ 1.0 1 1

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

  • Output: fact_paths.path, fact_paths.path_array, NULL::character varying(1024)
34. 0.020 0.020 ↑ 1.0 1 1

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

  • Output: fact_paths.path, fact_paths.path_array, NULL::character varying(1024)
  • Index Cond: (fact_paths.path = 'operatingsystem'::text)
35. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on public.factsets factsets_1 (cost=0.00..1.03 rows=3 width=1,071) (actual time=0.002..0.003 rows=3 loops=1)

  • Output: factsets_1.id, factsets_1.certname, factsets_1."timestamp", factsets_1.environment_id, factsets_1.hash, factsets_1.producer_timestamp, factsets_1.producer_id, factsets_1.paths_hash, factsets_1.stable, factsets_1.stable_hash, factsets_1.volatile
36. 5.244 5.274 ↓ 2.0 2 3

Nested Loop (cost=0.27..9.56 rows=1 width=34) (actual time=0.957..1.758 rows=2 loops=3)

  • Output: factsets.certname
  • Join Filter: ((jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array) IS NOT NULL) AND (jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array)) <> 'object'::text) AND (((jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array)) = 'number'::text) AND (jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array) = '7'::jsonb)) OR ((jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array)) = 'number'::text) AND (jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array) = '7.0'::jsonb)) OR ((jsonb_typeof(jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array)) = 'string'::text) AND (jsonb_extract_path((factsets.stable || factsets.volatile), VARIADIC fact_paths_1.path_array) = '"7"'::jsonb))))
37. 0.003 0.024 ↑ 1.0 1 3

Unique (cost=0.27..8.29 rows=1 width=598) (actual time=0.007..0.008 rows=1 loops=3)

  • Output: fact_paths_1.path, fact_paths_1.path_array, NULL::character varying(1024)
38. 0.021 0.021 ↑ 1.0 1 3

Index Scan using fact_paths_path_type_unique on public.fact_paths fact_paths_1 (cost=0.27..8.29 rows=1 width=598) (actual time=0.007..0.007 rows=1 loops=3)

  • Output: fact_paths_1.path, fact_paths_1.path_array, NULL::character varying(1024)
  • Index Cond: (fact_paths_1.path = 'operatingsystemmajrelease'::text)
39. 0.006 0.006 ↑ 1.5 2 3

Seq Scan on public.factsets (cost=0.00..1.03 rows=3 width=1,071) (actual time=0.001..0.002 rows=2 loops=3)

  • Output: factsets.id, factsets.certname, factsets."timestamp", factsets.environment_id, factsets.hash, factsets.producer_timestamp, factsets.producer_id, factsets.paths_hash, factsets.stable, factsets.stable_hash, factsets.volatile