explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dHoD

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1.086 ↓ 1.5 3 1

Limit (cost=238.89..238.94 rows=2 width=74) (actual time=1.081..1.086 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, (encode(reports.hash, 'hex'::text))
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.008 1.084 ↓ 1.5 3 1

Merge Left Join (cost=237.86..237.91 rows=2 width=74) (actual time=1.080..1.084 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, encode(reports.hash, 'hex'::text)
  • Merge Cond: (certnames.certname = catalogs.certname)
5. 0.012 1.066 ↓ 1.5 3 1

Sort (cost=236.80..236.81 rows=2 width=68) (actual time=1.066..1.066 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash
  • Sort Key: certnames.certname
  • Sort Method: quicksort Memory: 25kB
6. 0.163 1.054 ↓ 1.5 3 1

Hash Right Join (cost=1.09..236.79 rows=2 width=68) (actual time=1.024..1.054 rows=3 loops=1)

  • Output: certnames.certname, reports.end_time, reports.hash
  • Inner Unique: true
  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
7. 0.074 0.870 ↑ 1.5 943 1

Append (cost=0.00..228.26 rows=1,418 width=91) (actual time=0.004..0.870 rows=943 loops=1)

8. 0.001 0.001 ↓ 0.0 0 1

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

  • Output: reports.end_time, reports.hash, reports.certname, reports.id, reports.status_id, reports.environment_id
9. 0.087 0.087 ↓ 1.0 144 1

Seq Scan on public.reports_20200715z (cost=0.00..33.40 rows=140 width=87) (actual time=0.003..0.087 rows=144 loops=1)

  • Output: reports_20200715z.end_time, reports_20200715z.hash, reports_20200715z.certname, reports_20200715z.id, reports_20200715z.status_id, reports_20200715z.environment_id
10. 0.075 0.075 ↓ 1.0 96 1

Seq Scan on public.reports_20200716z (cost=0.00..20.93 rows=93 width=86) (actual time=0.006..0.075 rows=96 loops=1)

  • Output: reports_20200716z.end_time, reports_20200716z.hash, reports_20200716z.certname, reports_20200716z.id, reports_20200716z.status_id, reports_20200716z.environment_id
11. 0.051 0.051 ↓ 1.0 101 1

Seq Scan on public.reports_20200717z (cost=0.00..21.97 rows=97 width=87) (actual time=0.003..0.051 rows=101 loops=1)

  • Output: reports_20200717z.end_time, reports_20200717z.hash, reports_20200717z.certname, reports_20200717z.id, reports_20200717z.status_id, reports_20200717z.environment_id
12. 0.230 0.230 ↓ 1.1 137 1

Seq Scan on public.reports_20200718z (cost=0.00..29.27 rows=127 width=86) (actual time=0.003..0.230 rows=137 loops=1)

  • Output: reports_20200718z.end_time, reports_20200718z.hash, reports_20200718z.certname, reports_20200718z.id, reports_20200718z.status_id, reports_20200718z.environment_id
13. 0.067 0.067 ↓ 1.0 96 1

Seq Scan on public.reports_20200719z (cost=0.00..20.93 rows=93 width=86) (actual time=0.004..0.067 rows=96 loops=1)

  • Output: reports_20200719z.end_time, reports_20200719z.hash, reports_20200719z.certname, reports_20200719z.id, reports_20200719z.status_id, reports_20200719z.environment_id
14. 0.062 0.062 ↓ 1.0 96 1

Seq Scan on public.reports_20200720z (cost=0.00..20.93 rows=93 width=86) (actual time=0.009..0.062 rows=96 loops=1)

  • Output: reports_20200720z.end_time, reports_20200720z.hash, reports_20200720z.certname, reports_20200720z.id, reports_20200720z.status_id, reports_20200720z.environment_id
15. 0.081 0.081 ↓ 1.0 76 1

Seq Scan on public.reports_20200721z (cost=0.00..16.74 rows=74 width=86) (actual time=0.004..0.081 rows=76 loops=1)

  • Output: reports_20200721z.end_time, reports_20200721z.hash, reports_20200721z.certname, reports_20200721z.id, reports_20200721z.status_id, reports_20200721z.environment_id
16. 0.013 0.013 ↑ 8.8 16 1

Seq Scan on public.reports_20200724z (cost=0.00..11.40 rows=140 width=96) (actual time=0.004..0.013 rows=16 loops=1)

  • Output: reports_20200724z.end_time, reports_20200724z.hash, reports_20200724z.certname, reports_20200724z.id, reports_20200724z.status_id, reports_20200724z.environment_id
17. 0.046 0.046 ↑ 2.9 48 1

Seq Scan on public.reports_20200725z (cost=0.00..11.40 rows=140 width=96) (actual time=0.003..0.046 rows=48 loops=1)

  • Output: reports_20200725z.end_time, reports_20200725z.hash, reports_20200725z.certname, reports_20200725z.id, reports_20200725z.status_id, reports_20200725z.environment_id
18. 0.033 0.033 ↑ 2.9 48 1

Seq Scan on public.reports_20200726z (cost=0.00..11.40 rows=140 width=96) (actual time=0.004..0.033 rows=48 loops=1)

  • Output: reports_20200726z.end_time, reports_20200726z.hash, reports_20200726z.certname, reports_20200726z.id, reports_20200726z.status_id, reports_20200726z.environment_id
19. 0.032 0.032 ↑ 2.9 49 1

Seq Scan on public.reports_20200727z (cost=0.00..11.40 rows=140 width=96) (actual time=0.002..0.032 rows=49 loops=1)

  • Output: reports_20200727z.end_time, reports_20200727z.hash, reports_20200727z.certname, reports_20200727z.id, reports_20200727z.status_id, reports_20200727z.environment_id
20. 0.018 0.018 ↑ 3.9 36 1

Seq Scan on public.reports_20200728z (cost=0.00..11.40 rows=140 width=96) (actual time=0.003..0.018 rows=36 loops=1)

  • Output: reports_20200728z.end_time, reports_20200728z.hash, reports_20200728z.certname, reports_20200728z.id, reports_20200728z.status_id, reports_20200728z.environment_id
21. 0.003 0.021 ↓ 1.5 3 1

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

  • Output: certnames.certname, certnames.latest_report_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.014 0.018 ↓ 1.5 3 1

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

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

SubPlan (for Seq Scan)

24. 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
25. 0.006 0.010 ↑ 1.0 3 1

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

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

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