explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NbJe

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2.397 ↑ 1.0 1 1

GroupAggregate (cost=171.75..171.78 rows=1 width=52) (actual time=2.397..2.397 rows=1 loops=1)

  • Output: reports.cached_catalog_status, report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change, count(*)
  • Group Key: reports.cached_catalog_status, reports.corrective_change, reports.noop, reports.noop_pending, report_statuses.status
2.          

CTE not_active_nodes

3. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.certnames certnames_1 (cost=0.00..15.20 rows=520 width=32) (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: 1
4. 0.008 2.393 ↑ 1.0 1 1

Sort (cost=156.55..156.55 rows=1 width=44) (actual time=2.393..2.393 rows=1 loops=1)

  • Output: reports.cached_catalog_status, report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change
  • Sort Key: reports.cached_catalog_status, reports.corrective_change, reports.noop, reports.noop_pending, report_statuses.status
  • Sort Method: quicksort Memory: 25kB
5. 0.004 2.385 ↑ 1.0 1 1

Nested Loop Semi Join (cost=33.64..156.54 rows=1 width=44) (actual time=2.372..2.385 rows=1 loops=1)

  • Output: reports.cached_catalog_status, report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change
  • Join Filter: (certnames.certname = factsets.certname)
6. 0.002 1.528 ↑ 1.0 1 1

Nested Loop Semi Join (cost=33.22..147.89 rows=1 width=172) (actual time=1.516..1.528 rows=1 loops=1)

  • Output: certnames.certname, reports.cached_catalog_status, reports.noop, reports.noop_pending, reports.corrective_change, reports.certname, report_statuses.status, factsets_2.certname, factsets_1.certname
  • Join Filter: (certnames.certname = factsets_1.certname)
7. 0.002 0.681 ↑ 1.0 1 1

Nested Loop Semi Join (cost=32.80..139.21 rows=1 width=140) (actual time=0.669..0.681 rows=1 loops=1)

  • Output: certnames.certname, reports.cached_catalog_status, reports.noop, reports.noop_pending, reports.corrective_change, reports.certname, report_statuses.status, factsets_2.certname
  • Join Filter: (certnames.certname = factsets_2.certname)
8. 0.002 0.076 ↑ 1.0 1 1

Nested Loop Left Join (cost=32.38..130.56 rows=1 width=108) (actual time=0.064..0.076 rows=1 loops=1)

  • Output: certnames.certname, reports.cached_catalog_status, reports.noop, reports.noop_pending, reports.corrective_change, reports.certname, report_statuses.status
  • Inner Unique: true
9. 0.001 0.071 ↑ 1.0 1 1

Nested Loop Left Join (cost=32.25..130.35 rows=1 width=107) (actual time=0.060..0.071 rows=1 loops=1)

  • Output: certnames.certname, reports.cached_catalog_status, reports.noop, reports.noop_pending, reports.corrective_change, reports.certname, reports.status_id
10. 0.007 0.062 ↑ 1.0 1 1

Hash Join (cost=32.10..129.96 rows=1 width=107) (actual time=0.051..0.062 rows=1 loops=1)

  • Output: certnames.certname, reports.cached_catalog_status, reports.noop, reports.noop_pending, reports.corrective_change, reports.certname, reports.status_id
  • Inner Unique: true
  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
11. 0.003 0.036 ↑ 75.4 5 1

Append (cost=0.00..95.89 rows=377 width=91) (actual time=0.022..0.036 rows=5 loops=1)

12. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on public.reports (cost=0.00..0.00 rows=1 width=91) (actual time=0.005..0.006 rows=0 loops=1)

  • Output: reports.cached_catalog_status, reports.noop, reports.noop_pending, reports.corrective_change, reports.certname, reports.id, reports.status_id, reports.environment_id
  • Filter: (reports.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
13. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.reports_20200731z (cost=0.00..11.75 rows=47 width=91) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: reports_20200731z.cached_catalog_status, reports_20200731z.noop, reports_20200731z.noop_pending, reports_20200731z.corrective_change, reports_20200731z.certname, reports_20200731z.id, reports_20200731z.status_id, reports_20200731z.environment_id
  • Filter: (reports_20200731z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
14. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.reports_20200801z (cost=0.00..11.75 rows=47 width=91) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: reports_20200801z.cached_catalog_status, reports_20200801z.noop, reports_20200801z.noop_pending, reports_20200801z.corrective_change, reports_20200801z.certname, reports_20200801z.id, reports_20200801z.status_id, reports_20200801z.environment_id
  • Filter: (reports_20200801z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
15. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on public.reports_20200802z (cost=0.00..11.75 rows=47 width=91) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: reports_20200802z.cached_catalog_status, reports_20200802z.noop, reports_20200802z.noop_pending, reports_20200802z.corrective_change, reports_20200802z.certname, reports_20200802z.id, reports_20200802z.status_id, reports_20200802z.environment_id
  • Filter: (reports_20200802z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
16. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.reports_20200803z (cost=0.00..11.75 rows=47 width=91) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: reports_20200803z.cached_catalog_status, reports_20200803z.noop, reports_20200803z.noop_pending, reports_20200803z.corrective_change, reports_20200803z.certname, reports_20200803z.id, reports_20200803z.status_id, reports_20200803z.environment_id
  • Filter: (reports_20200803z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
17. 0.012 0.012 ↑ 9.4 5 1

Seq Scan on public.reports_20200804z (cost=0.00..11.75 rows=47 width=91) (actual time=0.008..0.012 rows=5 loops=1)

  • Output: reports_20200804z.cached_catalog_status, reports_20200804z.noop, reports_20200804z.noop_pending, reports_20200804z.corrective_change, reports_20200804z.certname, reports_20200804z.id, reports_20200804z.status_id, reports_20200804z.environment_id
  • Filter: (reports_20200804z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
18. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on public.reports_20200805z (cost=0.00..11.75 rows=47 width=91) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: reports_20200805z.cached_catalog_status, reports_20200805z.noop, reports_20200805z.noop_pending, reports_20200805z.corrective_change, reports_20200805z.certname, reports_20200805z.id, reports_20200805z.status_id, reports_20200805z.environment_id
  • Filter: (reports_20200805z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
19. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on public.reports_20200806z (cost=0.00..11.75 rows=47 width=91) (actual time=0.003..0.004 rows=0 loops=1)

  • Output: reports_20200806z.cached_catalog_status, reports_20200806z.noop, reports_20200806z.noop_pending, reports_20200806z.corrective_change, reports_20200806z.certname, reports_20200806z.id, reports_20200806z.status_id, reports_20200806z.environment_id
  • Filter: (reports_20200806z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
20. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on public.reports_20200807z (cost=0.00..11.75 rows=47 width=91) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: reports_20200807z.cached_catalog_status, reports_20200807z.noop, reports_20200807z.noop_pending, reports_20200807z.corrective_change, reports_20200807z.certname, reports_20200807z.id, reports_20200807z.status_id, reports_20200807z.environment_id
  • Filter: (reports_20200807z.end_time >= '2020-08-04 15:20:25.587+00'::timestamp with time zone)
21. 0.003 0.019 ↑ 260.0 1 1

Hash (cost=28.20..28.20 rows=260 width=40) (actual time=0.018..0.019 rows=1 loops=1)

  • Output: certnames.certname, certnames.latest_report_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.013 0.016 ↑ 260.0 1 1

Seq Scan on public.certnames (cost=11.70..28.20 rows=260 width=40) (actual time=0.016..0.016 rows=1 loops=1)

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

SubPlan (for Seq Scan)

24. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on not_active_nodes (cost=0.00..10.40 rows=520 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Output: not_active_nodes.certname
25. 0.008 0.008 ↑ 2.0 1 1

Index Scan using catalogs_certname_idx on public.catalogs (cost=0.15..0.37 rows=2 width=40) (actual time=0.007..0.008 rows=1 loops=1)

  • Output: catalogs.certname, catalogs.environment_id
  • Index Cond: (catalogs.certname = certnames.certname)
26. 0.003 0.003 ↑ 1.0 1 1

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

  • Output: report_statuses.id, report_statuses.status
  • Index Cond: (reports.status_id = report_statuses.id)
27. 0.589 0.603 ↑ 1.0 1 1

Nested Loop (cost=0.42..8.63 rows=1 width=32) (actual time=0.603..0.603 rows=1 loops=1)

  • Output: factsets_2.certname
  • Join Filter: ((jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths.path_array) IS NOT NULL) AND (jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths.path_array) = '"CentOS"'::jsonb) AND (jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths.path_array)) <> 'object'::text) AND (jsonb_typeof(jsonb_extract_path((factsets_2.stable || factsets_2.volatile), VARIADIC fact_paths.path_array)) = 'string'::text))
28. 0.005 0.005 ↑ 1.0 1 1

Index Scan using factsets_certname_idx on public.factsets factsets_2 (cost=0.15..0.29 rows=1 width=104) (actual time=0.005..0.005 rows=1 loops=1)

  • Output: factsets_2.id, factsets_2.certname, factsets_2."timestamp", factsets_2.environment_id, factsets_2.hash, factsets_2.producer_timestamp, factsets_2.producer_id, factsets_2.paths_hash, factsets_2.stable, factsets_2.stable_hash, factsets_2.volatile
  • Index Cond: (factsets_2.certname = reports.certname)
29. 0.000 0.009 ↑ 1.0 1 1

Unique (cost=0.27..8.29 rows=1 width=599) (actual time=0.009..0.009 rows=1 loops=1)

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

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

  • Output: fact_paths.path, fact_paths.path_array, NULL::character varying(1024)
  • Index Cond: (fact_paths.path = 'operatingsystem'::text)
31. 0.837 0.845 ↑ 1.0 1 1

Nested Loop (cost=0.42..8.67 rows=1 width=32) (actual time=0.845..0.845 rows=1 loops=1)

  • Output: factsets_1.certname
  • Join Filter: ((jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array) IS NOT NULL) 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)) = 'number'::text) AND (jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array) = '7'::jsonb)) OR ((jsonb_typeof(jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array)) = 'number'::text) AND (jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array) = '7.0'::jsonb)) OR ((jsonb_typeof(jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array)) = 'string'::text) AND (jsonb_extract_path((factsets_1.stable || factsets_1.volatile), VARIADIC fact_paths_1.path_array) = '"7"'::jsonb))))
32. 0.002 0.002 ↑ 1.0 1 1

Index Scan using factsets_certname_idx on public.factsets factsets_1 (cost=0.15..0.29 rows=1 width=104) (actual time=0.002..0.002 rows=1 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
  • Index Cond: (factsets_1.certname = reports.certname)
33. 0.001 0.006 ↑ 1.0 1 1

Unique (cost=0.27..8.29 rows=1 width=599) (actual time=0.006..0.006 rows=1 loops=1)

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

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

  • Output: fact_paths_1.path, fact_paths_1.path_array, NULL::character varying(1024)
  • Index Cond: (fact_paths_1.path = 'operatingsystemmajrelease'::text)
35. 0.841 0.853 ↑ 1.0 1 1

Nested Loop (cost=0.42..8.63 rows=1 width=32) (actual time=0.853..0.853 rows=1 loops=1)

  • Output: factsets.certname
  • 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) = '"Linux"'::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))
36. 0.002 0.002 ↑ 1.0 1 1

Index Scan using factsets_certname_idx on public.factsets (cost=0.15..0.29 rows=1 width=104) (actual time=0.002..0.002 rows=1 loops=1)

  • 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
  • Index Cond: (factsets.certname = reports.certname)
37. 0.000 0.010 ↑ 1.0 1 1

Unique (cost=0.27..8.29 rows=1 width=599) (actual time=0.010..0.010 rows=1 loops=1)

  • Output: fact_paths_2.path, fact_paths_2.path_array, NULL::character varying(1024)
38. 0.010 0.010 ↑ 1.0 1 1

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

  • Output: fact_paths_2.path, fact_paths_2.path_array, NULL::character varying(1024)
  • Index Cond: (fact_paths_2.path = 'kernel'::text)