explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AQ9Qc

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 2.326 ↓ 6.2 50 1

Limit (cost=72.83..72.85 rows=8 width=130) (actual time=2.319..2.326 rows=50 loops=1)

  • Output: reports.certname, environments.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.191 2.320 ↓ 6.2 50 1

Sort (cost=71.80..71.82 rows=8 width=130) (actual time=2.317..2.320 rows=50 loops=1)

  • Output: reports.certname, environments.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
  • Sort Method: top-N heapsort Memory: 38kB
5. 0.194 2.129 ↓ 41.2 330 1

Nested Loop Left Join (cost=3.21..71.68 rows=8 width=130) (actual time=1.090..2.129 rows=330 loops=1)

  • Output: reports.certname, environments.environment, reports.end_time, encode(reports.hash, 'hex'::text), report_statuses.status, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id
  • Inner Unique: true
  • Join Filter: (reports.status_id = report_statuses.id)
  • Rows Removed by Join Filter: 319
6. 0.105 1.935 ↓ 41.2 330 1

Nested Loop Left Join (cost=3.21..70.33 rows=8 width=121) (actual time=1.082..1.935 rows=330 loops=1)

  • Output: reports.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.status_id, environments.environment
  • Inner Unique: true
  • Join Filter: (environments.id = reports.environment_id)
7. 0.064 1.830 ↓ 41.2 330 1

Nested Loop (cost=3.21..69.11 rows=8 width=115) (actual time=1.077..1.830 rows=330 loops=1)

  • Output: reports.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.environment_id, reports.status_id
  • Join Filter: (reports.certname = fs.certname)
8. 0.009 1.034 ↓ 3.0 3 1

HashAggregate (cost=3.19..3.20 rows=1 width=68) (actual time=1.032..1.034 rows=3 loops=1)

  • Output: fs.certname, certnames.certname
  • Group Key: certnames.certname
9. 0.011 1.025 ↓ 3.0 3 1

Nested Loop (cost=0.00..3.19 rows=1 width=68) (actual time=0.410..1.025 rows=3 loops=1)

  • Output: fs.certname, certnames.certname
  • Inner Unique: true
  • Join Filter: (fs.certname = certnames.certname)
  • Rows Removed by Join Filter: 3
10. 1.008 1.008 ↓ 3.0 3 1

Seq Scan on public.factsets fs (cost=0.00..2.12 rows=1 width=42) (actual time=0.403..1.008 rows=3 loops=1)

  • Output: fs.id, fs.certname, fs."timestamp", fs.environment_id, fs.hash, fs.producer_timestamp, fs.producer_id, fs.paths_hash, fs.stable, fs.stable_hash, fs.volatile
  • Filter: (((fs.stable || fs.volatile) @> '{"operatingsystem": "CentOS"}'::jsonb) AND ((fs.stable || fs.volatile) @> '{"operatingsystemmajrelease": "7"}'::jsonb))
11. 0.006 0.006 ↑ 1.5 2 3

Seq Scan on public.certnames (cost=0.00..1.03 rows=3 width=34) (actual time=0.002..0.002 rows=2 loops=3)

  • Output: certnames.id, certnames.certname, certnames.latest_report_id, certnames.deactivated, certnames.expired, certnames.package_hash, certnames.latest_report_timestamp, certnames.catalog_inputs_timestamp, certnames.catalog_inputs_uuid
12. 0.051 0.732 ↓ 3.9 110 3

Append (cost=0.02..65.56 rows=28 width=123) (actual time=0.026..0.244 rows=110 loops=3)

13. 0.000 0.003 ↓ 0.0 0 3

Seq Scan on public.reports (cost=0.02..0.02 rows=1 width=131) (actual time=0.001..0.001 rows=0 loops=3)

  • Output: reports.certname, reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.environment_id, reports.producer_id, reports.status_id
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports.report_type = 'agent'::text) AND (certnames.certname = reports.certname))
14.          

SubPlan (for Seq Scan)

15. 0.009 0.009 ↓ 0.0 0 9

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

  • Output: not_active_nodes.certname
16. 0.174 0.207 ↓ 1.3 25 3

Bitmap Heap Scan on public.reports_20200721z (cost=3.12..16.76 rows=19 width=121) (actual time=0.022..0.069 rows=25 loops=3)

  • Output: reports_20200721z.certname, reports_20200721z.end_time, reports_20200721z.hash, reports_20200721z.noop, reports_20200721z.noop_pending, reports_20200721z.corrective_change, reports_20200721z.job_id, reports_20200721z.environment_id, reports_20200721z.producer_id, reports_20200721z.status_id
  • Recheck Cond: (reports_20200721z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200721z.report_type = 'agent'::text))
  • Heap Blocks: exact=31
17. 0.033 0.033 ↑ 1.5 25 3

Bitmap Index Scan on reports_certname_idx_20200721z (cost=0.00..3.09 rows=38 width=0) (actual time=0.011..0.011 rows=25 loops=3)

  • Index Cond: (reports_20200721z.certname = certnames.certname)
18. 0.039 0.039 ↓ 5.0 5 3

Seq Scan on public.reports_20200724z (cost=0.02..4.30 rows=1 width=131) (actual time=0.008..0.013 rows=5 loops=3)

  • Output: reports_20200724z.certname, reports_20200724z.end_time, reports_20200724z.hash, reports_20200724z.noop, reports_20200724z.noop_pending, reports_20200724z.corrective_change, reports_20200724z.job_id, reports_20200724z.environment_id, reports_20200724z.producer_id, reports_20200724z.status_id
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200724z.report_type = 'agent'::text) AND (certnames.certname = reports_20200724z.certname))
  • Rows Removed by Filter: 11
19. 0.069 0.069 ↓ 16.0 16 3

Index Scan using reports_certname_idx_20200725z on public.reports_20200725z (cost=0.16..6.85 rows=1 width=131) (actual time=0.007..0.023 rows=16 loops=3)

  • Output: reports_20200725z.certname, reports_20200725z.end_time, reports_20200725z.hash, reports_20200725z.noop, reports_20200725z.noop_pending, reports_20200725z.corrective_change, reports_20200725z.job_id, reports_20200725z.environment_id, reports_20200725z.producer_id, reports_20200725z.status_id
  • Index Cond: (reports_20200725z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200725z.report_type = 'agent'::text))
20. 0.069 0.069 ↓ 16.0 16 3

Index Scan using reports_certname_idx_20200726z on public.reports_20200726z (cost=0.16..6.85 rows=1 width=131) (actual time=0.008..0.023 rows=16 loops=3)

  • Output: reports_20200726z.certname, reports_20200726z.end_time, reports_20200726z.hash, reports_20200726z.noop, reports_20200726z.noop_pending, reports_20200726z.corrective_change, reports_20200726z.job_id, reports_20200726z.environment_id, reports_20200726z.producer_id, reports_20200726z.status_id
  • Index Cond: (reports_20200726z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200726z.report_type = 'agent'::text))
21. 0.069 0.069 ↓ 16.0 16 3

Index Scan using reports_certname_idx_20200727z on public.reports_20200727z (cost=0.16..6.85 rows=1 width=131) (actual time=0.006..0.023 rows=16 loops=3)

  • Output: reports_20200727z.certname, reports_20200727z.end_time, reports_20200727z.hash, reports_20200727z.noop, reports_20200727z.noop_pending, reports_20200727z.corrective_change, reports_20200727z.job_id, reports_20200727z.environment_id, reports_20200727z.producer_id, reports_20200727z.status_id
  • Index Cond: (reports_20200727z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200727z.report_type = 'agent'::text))
22. 0.060 0.060 ↓ 13.0 13 3

Index Scan using reports_certname_idx_20200728z on public.reports_20200728z (cost=0.16..6.85 rows=1 width=131) (actual time=0.008..0.020 rows=13 loops=3)

  • Output: reports_20200728z.certname, reports_20200728z.end_time, reports_20200728z.hash, reports_20200728z.noop, reports_20200728z.noop_pending, reports_20200728z.corrective_change, reports_20200728z.job_id, reports_20200728z.environment_id, reports_20200728z.producer_id, reports_20200728z.status_id
  • Index Cond: (reports_20200728z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200728z.report_type = 'agent'::text))
23. 0.084 0.084 ↓ 4.0 4 3

Seq Scan on public.reports_20200729z (cost=0.02..3.21 rows=1 width=131) (actual time=0.025..0.028 rows=4 loops=3)

  • Output: reports_20200729z.certname, reports_20200729z.end_time, reports_20200729z.hash, reports_20200729z.noop, reports_20200729z.noop_pending, reports_20200729z.corrective_change, reports_20200729z.job_id, reports_20200729z.environment_id, reports_20200729z.producer_id, reports_20200729z.status_id
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200729z.report_type = 'agent'::text) AND (certnames.certname = reports_20200729z.certname))
  • Rows Removed by Filter: 7
24. 0.060 0.060 ↓ 14.0 14 3

Index Scan using reports_certname_idx_20200730z on public.reports_20200730z (cost=0.16..6.85 rows=1 width=131) (actual time=0.008..0.020 rows=14 loops=3)

  • Output: reports_20200730z.certname, reports_20200730z.end_time, reports_20200730z.hash, reports_20200730z.noop, reports_20200730z.noop_pending, reports_20200730z.corrective_change, reports_20200730z.job_id, reports_20200730z.environment_id, reports_20200730z.producer_id, reports_20200730z.status_id
  • Index Cond: (reports_20200730z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200730z.report_type = 'agent'::text))
25. 0.021 0.021 ↑ 1.0 1 3

Index Scan using reports_certname_idx_20200803z on public.reports_20200803z (cost=0.17..6.86 rows=1 width=131) (actual time=0.005..0.007 rows=1 loops=3)

  • Output: reports_20200803z.certname, reports_20200803z.end_time, reports_20200803z.hash, reports_20200803z.noop, reports_20200803z.noop_pending, reports_20200803z.corrective_change, reports_20200803z.job_id, reports_20200803z.environment_id, reports_20200803z.producer_id, reports_20200803z.status_id
  • Index Cond: (reports_20200803z.certname = certnames.certname)
  • Filter: ((NOT (hashed SubPlan 2)) AND (reports_20200803z.report_type = 'agent'::text))
26. 0.000 0.000 ↑ 2.0 1 330

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

  • Output: environments.environment, environments.id
27. 0.003 0.003 ↑ 2.0 1 1

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

  • Output: environments.environment, environments.id
28. 0.000 0.000 ↑ 1.5 2 330

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

  • Output: report_statuses.status, report_statuses.id
29. 0.003 0.003 ↑ 1.0 3 1

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

  • Output: report_statuses.status, report_statuses.id