explain.depesz.com

PostgreSQL's explain analyze made readable

Result: osjHf

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 25,576.536 ↑ 1.0 1 1

Aggregate (cost=2,931,587.81..2,931,587.82 rows=1 width=8) (actual time=25,576.536..25,576.536 rows=1 loops=1)

2.          

CTE not_active_nodes

3. 6.478 6.478 ↑ 1.0 902 1

Seq Scan on certnames certnames_1 (cost=0.00..736.70 rows=902 width=29) (actual time=0.005..6.478 rows=902 loops=1)

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 21,468
4. 0.035 25,576.519 ↓ 8.0 8 1

Nested Loop Left Join (cost=981.11..2,930,851.10 rows=1 width=0) (actual time=25,575.764..25,576.519 rows=8 loops=1)

5. 3,571.301 25,576.260 ↓ 8.0 8 1

Hash Right Join (cost=980.70..2,930,847.51 rows=1 width=29) (actual time=25,575.647..25,576.260 rows=8 loops=1)

  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
  • Filter: (reports.end_time IS NULL)
  • Rows Removed by Filter: 21,460
6. 919.421 21,986.087 ↑ 1.0 13,809,940 1

Append (cost=0.00..2,857,326.64 rows=13,817,176 width=61) (actual time=0.044..21,986.087 rows=13,809,940 loops=1)

7. 258.668 258.668 ↑ 1.0 134,087 1

Seq Scan on reports (cost=0.00..28,461.24 rows=134,424 width=60) (actual time=0.041..258.668 rows=134,087 loops=1)

8. 1,827.348 1,827.348 ↑ 1.0 1,041,338 1

Seq Scan on reports_20200715z (cost=0.00..210,229.60 rows=1,043,760 width=61) (actual time=0.019..1,827.348 rows=1,041,338 loops=1)

9. 1,726.959 1,726.959 ↑ 1.0 1,007,520 1

Seq Scan on reports_20200716z (cost=0.00..203,597.38 rows=1,008,938 width=61) (actual time=0.026..1,726.959 rows=1,007,520 loops=1)

10. 1,705.760 1,705.760 ↑ 1.0 992,097 1

Seq Scan on reports_20200717z (cost=0.00..200,888.97 rows=992,897 width=61) (actual time=0.034..1,705.760 rows=992,097 loops=1)

11. 1,554.395 1,554.395 ↑ 1.0 992,888 1

Seq Scan on reports_20200718z (cost=0.00..200,217.19 rows=993,719 width=61) (actual time=0.037..1,554.395 rows=992,888 loops=1)

12. 1,756.746 1,756.746 ↑ 1.0 995,597 1

Seq Scan on reports_20200719z (cost=0.00..200,597.99 rows=996,399 width=61) (actual time=0.026..1,756.746 rows=995,597 loops=1)

13. 1,611.750 1,611.750 ↑ 1.0 998,596 1

Seq Scan on reports_20200720z (cost=0.00..201,483.38 rows=998,838 width=61) (actual time=0.029..1,611.750 rows=998,596 loops=1)

14. 1,591.657 1,591.657 ↑ 1.0 1,000,775 1

Seq Scan on reports_20200721z (cost=0.00..201,718.99 rows=1,001,099 width=61) (actual time=0.026..1,591.657 rows=1,000,775 loops=1)

15. 1,544.888 1,544.888 ↑ 1.0 1,006,112 1

Seq Scan on reports_20200722z (cost=0.00..202,687.85 rows=1,007,085 width=61) (actual time=0.040..1,544.888 rows=1,006,112 loops=1)

16. 1,257.991 1,257.991 ↑ 1.0 1,004,327 1

Seq Scan on reports_20200723z (cost=0.00..202,267.57 rows=1,004,357 width=61) (actual time=0.027..1,257.991 rows=1,004,327 loops=1)

17. 1,032.089 1,032.089 ↓ 1.0 739,229 1

Seq Scan on reports_20200724z (cost=0.00..152,005.03 rows=738,103 width=61) (actual time=0.026..1,032.089 rows=739,229 loops=1)

18. 1,394.317 1,394.317 ↓ 1.0 1,003,158 1

Seq Scan on reports_20200725z (cost=0.00..201,535.49 rows=1,003,149 width=61) (actual time=0.042..1,394.317 rows=1,003,158 loops=1)

19. 1,248.519 1,248.519 ↑ 1.0 999,272 1

Seq Scan on reports_20200726z (cost=0.00..200,990.34 rows=999,534 width=61) (actual time=0.026..1,248.519 rows=999,272 loops=1)

20. 1,282.739 1,282.739 ↓ 1.0 1,006,712 1

Seq Scan on reports_20200727z (cost=0.00..202,568.20 rows=1,006,320 width=61) (actual time=0.028..1,282.739 rows=1,006,712 loops=1)

21. 1,272.840 1,272.840 ↑ 1.0 888,232 1

Seq Scan on reports_20200728z (cost=0.00..178,991.54 rows=888,554 width=61) (actual time=0.034..1,272.840 rows=888,232 loops=1)

22. 5.532 18.872 ↓ 1.9 21,468 1

Hash (cost=812.92..812.92 rows=11,185 width=37) (actual time=18.872..18.872 rows=21,468 loops=1)

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,782kB
23. 6.617 13.340 ↓ 1.9 21,468 1

Seq Scan on certnames (cost=20.29..812.92 rows=11,185 width=37) (actual time=7.075..13.340 rows=21,468 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 902
24.          

SubPlan (for Seq Scan)

25. 6.723 6.723 ↑ 1.0 902 1

CTE Scan on not_active_nodes (cost=0.00..18.04 rows=902 width=32) (actual time=0.007..6.723 rows=902 loops=1)

26. 0.224 0.224 ↑ 1.0 1 8

Index Scan using catalogs_certname_idx on catalogs (cost=0.41..3.58 rows=1 width=37) (actual time=0.028..0.028 rows=1 loops=8)

  • Index Cond: (certname = certnames.certname)
Planning time : 18.382 ms
Execution time : 25,577.133 ms