explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VqYJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.271 ↓ 3.0 3 1

Limit (cost=28.39..28.39 rows=1 width=131) (actual time=0.270..0.271 rows=3 loops=1)

2.          

CTE not_active_nodes

3. 0.003 0.003 ↓ 0.0 0 1

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

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

Sort (cost=27.36..27.36 rows=1 width=131) (actual time=0.269..0.270 rows=3 loops=1)

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

Nested Loop Left Join (cost=1.35..27.35 rows=1 width=131) (actual time=0.217..0.240 rows=3 loops=1)

6. 0.004 0.231 ↓ 3.0 3 1

Nested Loop Left Join (cost=1.22..26.98 rows=1 width=125) (actual time=0.211..0.231 rows=3 loops=1)

7. 0.005 0.218 ↓ 3.0 3 1

Nested Loop Left Join (cost=1.09..26.63 rows=1 width=125) (actual time=0.203..0.218 rows=3 loops=1)

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

Hash Join (cost=1.09..25.56 rows=1 width=125) (actual time=0.197..0.204 rows=3 loops=1)

  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
9. 0.009 0.151 ↑ 1.2 82 1

Append (cost=0.00..23.98 rows=95 width=131) (actual time=0.017..0.151 rows=82 loops=1)

10. 0.005 0.005 ↓ 0.0 0 1

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

  • Filter: (producer_timestamp > '2020-07-27 02:41:58.235+00'::timestamp with time zone)
11. 0.076 0.076 ↑ 1.1 44 1

Seq Scan on reports_20200727z (cost=0.00..11.75 rows=47 width=131) (actual time=0.011..0.076 rows=44 loops=1)

  • Filter: (producer_timestamp > '2020-07-27 02:41:58.235+00'::timestamp with time zone)
  • Rows Removed by Filter: 5
12. 0.061 0.061 ↑ 1.2 38 1

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

  • Filter: (producer_timestamp > '2020-07-27 02:41:58.235+00'::timestamp with time zone)
13. 0.004 0.018 ↓ 1.5 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.010 0.014 ↓ 1.5 3 1

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

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

SubPlan (for Seq Scan)

16. 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)

17. 0.009 0.009 ↑ 1.0 3 3

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

18. 0.009 0.009 ↑ 1.0 1 3

Index Scan using report_statuses_pkey on report_statuses (cost=0.13..0.27 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=3)

  • Index Cond: (reports.status_id = id)
19. 0.003 0.003 ↑ 1.0 1 3

Index Scan using environments_pkey on environments reports_environment (cost=0.13..0.27 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=3)

  • Index Cond: (id = reports.environment_id)