explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3YGT

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

Limit (cost=239.79..239.80 rows=1 width=131) (actual time=3.751..3.752 rows=3 loops=1)

2.          

CTE not_active_nodes

3. 0.004 0.004 ↓ 0.0 0 1

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

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 3
4. 0.019 3.750 ↓ 3.0 3 1

Sort (cost=238.76..238.77 rows=1 width=131) (actual time=3.749..3.750 rows=3 loops=1)

  • Sort Key: reports.end_time DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.067 3.731 ↓ 3.0 3 1

Nested Loop Left Join (cost=1.09..238.75 rows=1 width=131) (actual time=3.664..3.731 rows=3 loops=1)

  • Join Filter: (reports_environment.id = reports.environment_id)
6. 0.007 3.661 ↓ 3.0 3 1

Nested Loop Left Join (cost=1.09..237.71 rows=1 width=117) (actual time=3.600..3.661 rows=3 loops=1)

  • Join Filter: (reports.status_id = report_statuses.id)
  • Rows Removed by Join Filter: 2
7. 0.007 3.642 ↓ 3.0 3 1

Nested Loop Left Join (cost=1.09..236.64 rows=1 width=117) (actual time=3.588..3.642 rows=3 loops=1)

  • Join Filter: (catalogs.certname = certnames.certname)
  • Rows Removed by Join Filter: 6
8. 0.243 3.599 ↓ 3.0 3 1

Hash Join (cost=1.09..235.57 rows=1 width=117) (actual time=3.580..3.599 rows=3 loops=1)

  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
9. 0.126 3.333 ↑ 1.0 945 1

Append (cost=0.00..229.48 rows=953 width=124) (actual time=0.016..3.333 rows=945 loops=1)

10. 0.006 0.006 ↓ 0.0 0 1

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

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
11. 0.453 0.453 ↓ 1.0 144 1

Seq Scan on reports_20200715z (cost=0.00..33.75 rows=140 width=122) (actual time=0.009..0.453 rows=144 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
12. 0.442 0.442 ↓ 1.0 96 1

Seq Scan on reports_20200716z (cost=0.00..21.16 rows=93 width=121) (actual time=0.005..0.442 rows=96 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
13. 0.321 0.321 ↓ 1.0 101 1

Seq Scan on reports_20200717z (cost=0.00..22.21 rows=97 width=122) (actual time=0.005..0.321 rows=101 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
14. 0.445 0.445 ↓ 1.1 137 1

Seq Scan on reports_20200718z (cost=0.00..29.59 rows=127 width=121) (actual time=0.004..0.445 rows=137 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
15. 0.327 0.327 ↓ 1.0 96 1

Seq Scan on reports_20200719z (cost=0.00..21.16 rows=93 width=121) (actual time=0.026..0.327 rows=96 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
16. 0.304 0.304 ↓ 1.0 96 1

Seq Scan on reports_20200720z (cost=0.00..21.16 rows=93 width=121) (actual time=0.015..0.304 rows=96 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
17. 0.258 0.258 ↓ 1.0 76 1

Seq Scan on reports_20200721z (cost=0.00..16.93 rows=74 width=121) (actual time=0.026..0.258 rows=76 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
18. 0.126 0.126 ↑ 2.9 16 1

Seq Scan on reports_20200724z (cost=0.00..11.75 rows=47 width=131) (actual time=0.015..0.126 rows=16 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
19. 0.141 0.141 ↓ 1.0 48 1

Seq Scan on reports_20200725z (cost=0.00..11.75 rows=47 width=131) (actual time=0.017..0.141 rows=48 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
20. 0.133 0.133 ↓ 1.0 48 1

Seq Scan on reports_20200726z (cost=0.00..11.75 rows=47 width=131) (actual time=0.015..0.133 rows=48 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
21. 0.128 0.128 ↓ 1.0 49 1

Seq Scan on reports_20200727z (cost=0.00..11.75 rows=47 width=131) (actual time=0.018..0.128 rows=49 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
22. 0.123 0.123 ↑ 1.2 38 1

Seq Scan on reports_20200728z (cost=0.00..11.75 rows=47 width=131) (actual time=0.014..0.123 rows=38 loops=1)

  • Filter: (producer_timestamp > '2020-07-14 02:41:58.235+00'::timestamp with time zone)
23. 0.006 0.023 ↓ 1.5 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.012 0.017 ↓ 1.5 3 1

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

  • Filter: (NOT (hashed SubPlan 2))
25.          

SubPlan (for Seq Scan)

26. 0.005 0.005 ↓ 0.0 0 1

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

27. 0.036 0.036 ↑ 1.0 3 3

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

28. 0.012 0.012 ↑ 1.5 2 3

Seq Scan on report_statuses (cost=0.00..1.03 rows=3 width=16) (actual time=0.004..0.004 rows=2 loops=3)

29. 0.003 0.003 ↑ 2.0 1 3

Seq Scan on environments reports_environment (cost=0.00..1.02 rows=2 width=22) (actual time=0.001..0.001 rows=1 loops=3)