explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FERb

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 27,247.560 ↑ 1.0 50 1

Limit (cost=2,942,890.08..2,942,890.21 rows=50 width=111) (actual time=27,247.554..27,247.560 rows=50 loops=1)

2.          

CTE not_active_nodes

3. 5.753 5.753 ↑ 1.0 902 1

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

  • Filter: ((deactivated IS NOT NULL) OR (expired IS NOT NULL))
  • Rows Removed by Filter: 21,468
4. 6.664 27,247.541 ↑ 223.7 50 1

Sort (cost=2,942,153.38..2,942,181.35 rows=11,185 width=111) (actual time=27,247.538..27,247.541 rows=50 loops=1)

  • Sort Key: reports.end_time DESC
  • Sort Method: top-N heapsort Memory: 47kB
5. 8.047 27,240.877 ↓ 1.9 21,468 1

Nested Loop Left Join (cost=10,036.64..2,941,781.82 rows=11,185 width=111) (actual time=8,047.199..27,240.877 rows=21,468 loops=1)

  • Join Filter: (reports_environment.id = reports.environment_id)
  • Rows Removed by Join Filter: 48
6. 6.819 27,232.830 ↓ 1.9 21,468 1

Nested Loop Left Join (cost=10,036.64..2,940,377.02 rows=11,185 width=96) (actual time=8,047.111..27,232.830 rows=21,468 loops=1)

  • Join Filter: (reports.status_id = report_statuses.id)
  • Rows Removed by Join Filter: 2,833
7. 15.389 27,226.011 ↓ 1.9 21,468 1

Hash Left Join (cost=10,036.64..2,939,956.54 rows=11,185 width=96) (actual time=8,047.001..27,226.011 rows=21,468 loops=1)

  • Hash Cond: (certnames.certname = catalogs.certname)
8. 3,576.076 27,125.599 ↓ 1.9 21,468 1

Hash Right Join (cost=980.70..2,930,746.89 rows=11,185 width=96) (actual time=7,961.599..27,125.599 rows=21,468 loops=1)

  • Hash Cond: ((reports.certname = certnames.certname) AND (reports.id = certnames.latest_report_id))
9. 979.490 23,531.326 ↑ 1.0 13,809,463 1

Append (cost=0.00..2,857,228.52 rows=13,816,701 width=104) (actual time=0.044..23,531.326 rows=13,809,463 loops=1)

10. 278.999 278.999 ↑ 1.0 134,087 1

Seq Scan on reports (cost=0.00..28,461.24 rows=134,424 width=116) (actual time=0.042..278.999 rows=134,087 loops=1)

11. 1,949.363 1,949.363 ↑ 1.0 1,041,338 1

Seq Scan on reports_20200715z (cost=0.00..210,229.60 rows=1,043,760 width=117) (actual time=0.031..1,949.363 rows=1,041,338 loops=1)

12. 1,879.617 1,879.617 ↑ 1.0 1,007,520 1

Seq Scan on reports_20200716z (cost=0.00..203,597.38 rows=1,008,938 width=91) (actual time=0.034..1,879.617 rows=1,007,520 loops=1)

13. 1,920.471 1,920.471 ↑ 1.0 992,097 1

Seq Scan on reports_20200717z (cost=0.00..200,888.97 rows=992,897 width=91) (actual time=0.069..1,920.471 rows=992,097 loops=1)

14. 1,923.848 1,923.848 ↑ 1.0 992,888 1

Seq Scan on reports_20200718z (cost=0.00..200,217.19 rows=993,719 width=91) (actual time=0.075..1,923.848 rows=992,888 loops=1)

15. 1,753.350 1,753.350 ↑ 1.0 995,597 1

Seq Scan on reports_20200719z (cost=0.00..200,597.99 rows=996,399 width=91) (actual time=0.071..1,753.350 rows=995,597 loops=1)

16. 1,828.651 1,828.651 ↑ 1.0 998,596 1

Seq Scan on reports_20200720z (cost=0.00..201,483.38 rows=998,838 width=117) (actual time=0.041..1,828.651 rows=998,596 loops=1)

17. 1,771.447 1,771.447 ↑ 1.0 1,000,775 1

Seq Scan on reports_20200721z (cost=0.00..201,718.99 rows=1,001,099 width=117) (actual time=0.094..1,771.447 rows=1,000,775 loops=1)

18. 1,639.690 1,639.690 ↑ 1.0 1,006,112 1

Seq Scan on reports_20200722z (cost=0.00..202,687.85 rows=1,007,085 width=91) (actual time=0.069..1,639.690 rows=1,006,112 loops=1)

19. 1,303.978 1,303.978 ↑ 1.0 1,004,327 1

Seq Scan on reports_20200723z (cost=0.00..202,267.57 rows=1,004,357 width=91) (actual time=0.047..1,303.978 rows=1,004,327 loops=1)

20. 954.706 954.706 ↓ 1.0 739,229 1

Seq Scan on reports_20200724z (cost=0.00..152,005.03 rows=738,103 width=91) (actual time=0.044..954.706 rows=739,229 loops=1)

21. 1,358.324 1,358.324 ↓ 1.0 1,003,158 1

Seq Scan on reports_20200725z (cost=0.00..201,535.49 rows=1,003,149 width=117) (actual time=0.072..1,358.324 rows=1,003,158 loops=1)

22. 1,423.995 1,423.995 ↑ 1.0 999,272 1

Seq Scan on reports_20200726z (cost=0.00..200,990.34 rows=999,534 width=117) (actual time=0.052..1,423.995 rows=999,272 loops=1)

23. 1,297.571 1,297.571 ↓ 1.0 1,006,712 1

Seq Scan on reports_20200727z (cost=0.00..202,568.20 rows=1,006,320 width=117) (actual time=0.065..1,297.571 rows=1,006,712 loops=1)

24. 1,267.826 1,267.826 ↑ 1.0 887,755 1

Seq Scan on reports_20200728z (cost=0.00..178,895.79 rows=888,079 width=117) (actual time=0.038..1,267.826 rows=887,755 loops=1)

25. 5.745 18.197 ↓ 1.9 21,468 1

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

  • Buckets: 32,768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1,782kB
26. 6.454 12.452 ↓ 1.9 21,468 1

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

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

SubPlan (for Seq Scan)

28. 5.998 5.998 ↑ 1.0 902 1

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

29. 6.125 85.023 ↑ 1.0 22,353 1

Hash (cost=8,776.53..8,776.53 rows=22,353 width=37) (actual time=85.023..85.023 rows=22,353 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,846kB
30. 78.898 78.898 ↑ 1.0 22,353 1

Seq Scan on catalogs (cost=0.00..8,776.53 rows=22,353 width=37) (actual time=0.055..78.898 rows=22,353 loops=1)

31. 0.000 0.000 ↑ 3.0 1 21,468

Materialize (cost=0.00..1.04 rows=3 width=16) (actual time=0.000..0.000 rows=1 loops=21,468)

32. 0.065 0.065 ↑ 1.0 3 1

Seq Scan on report_statuses (cost=0.00..1.03 rows=3 width=16) (actual time=0.061..0.065 rows=3 loops=1)

33. 0.000 0.000 ↑ 9.0 1 21,468

Materialize (cost=0.00..1.14 rows=9 width=20) (actual time=0.000..0.000 rows=1 loops=21,468)

34. 0.030 0.030 ↑ 1.5 6 1

Seq Scan on environments reports_environment (cost=0.00..1.09 rows=9 width=20) (actual time=0.021..0.030 rows=6 loops=1)

Planning time : 20.892 ms
Execution time : 27,248.517 ms