explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wyQL

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.110 ↑ 50.0 1 1

Limit (cost=146.02..146.15 rows=50 width=171) (actual time=0.110..0.110 rows=1 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 inactive_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.031 0.109 ↑ 260.0 1 1

Sort (cost=130.82..131.47 rows=260 width=171) (actual time=0.109..0.109 rows=1 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.013 0.078 ↑ 260.0 1 1

Hash Right Join (cost=92.40..122.18 rows=260 width=171) (actual time=0.073..0.078 rows=1 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
  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
6. 0.006 0.036 ↑ 30.0 5 1

Hash Right Join (cost=43.25..71.25 rows=150 width=179) (actual time=0.033..0.036 rows=5 loops=1)

  • Output: reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.certname, reports.id, report_statuses.status, reports_environment.environment
  • Hash Cond: (reports_environment.id = reports.environment_id)
7. 0.008 0.008 ↑ 1,200.0 1 1

Seq Scan on public.environments reports_environment (cost=0.00..22.00 rows=1,200 width=40) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: reports_environment.id, reports_environment.environment
8. 0.002 0.022 ↑ 30.0 5 1

Hash (cost=41.38..41.38 rows=150 width=155) (actual time=0.022..0.022 rows=5 loops=1)

  • Output: reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.certname, reports.id, reports.environment_id, report_statuses.status
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.007 0.020 ↑ 30.0 5 1

Hash Right Join (cost=13.38..41.38 rows=150 width=155) (actual time=0.016..0.020 rows=5 loops=1)

  • Output: reports.end_time, reports.hash, reports.noop, reports.noop_pending, reports.corrective_change, reports.job_id, reports.certname, reports.id, reports.environment_id, report_statuses.status
  • Hash Cond: (report_statuses.id = reports.status_id)
10. 0.003 0.003 ↑ 600.0 2 1

Seq Scan on public.report_statuses (cost=0.00..22.00 rows=1,200 width=40) (actual time=0.003..0.003 rows=2 loops=1)

  • Output: report_statuses.id, report_statuses.status
11. 0.004 0.010 ↑ 30.0 5 1

Hash (cost=11.50..11.50 rows=150 width=131) (actual time=0.010..0.010 rows=5 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
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.006 0.006 ↑ 30.0 5 1

Seq Scan on public.reports (cost=0.00..11.50 rows=150 width=131) (actual time=0.004..0.006 rows=5 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
13. 0.000 0.029 ↑ 260.0 1 1

Hash (cost=45.25..45.25 rows=260 width=40) (actual time=0.029..0.029 rows=1 loops=1)

  • Output: certnames.certname, certnames.latest_report_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.007 0.029 ↑ 260.0 1 1

Hash Right Join (cost=31.45..45.25 rows=260 width=40) (actual time=0.026..0.029 rows=1 loops=1)

  • Output: certnames.certname, certnames.latest_report_id
  • Inner Unique: true
  • Hash Cond: (catalogs.certname = certnames.certname)
15. 0.003 0.003 ↑ 300.0 1 1

Seq Scan on public.catalogs (cost=0.00..13.00 rows=300 width=40) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: catalogs.certname, catalogs.environment_id
16. 0.001 0.019 ↑ 260.0 1 1

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

  • Output: certnames.certname, certnames.latest_report_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.015 0.018 ↑ 260.0 1 1

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

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

SubPlan (for Seq Scan)

19. 0.003 0.003 ↓ 0.0 0 1

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

  • Output: inactive_nodes.certname