explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nXLS

Settings
# exclusive inclusive rows x rows loops node
1. 0.135 6,281.265 ↑ 1.1 9 1

Unique (cost=420,849.33..420,849.48 rows=10 width=312) (actual time=6,281.017..6,281.265 rows=9 loops=1)

  • Functions: 70
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 7.245 ms, Inlining 0.000 ms, Optimization 2.238 ms, Emission 51.805 ms, Total 61.288 ms
2. 6.880 6,281.130 ↓ 73.6 2,209 1

Sort (cost=420,849.33..420,849.40 rows=30 width=312) (actual time=6,281.015..6,281.130 rows=2,209 loops=1)

  • Sort Key: l_3.ld203_uuid, total_score.total_score DESC
  • Sort Method: quicksort Memory: 837kB
3. 6.139 6,274.250 ↓ 73.6 2,209 1

Nested Loop (cost=202,581.66..420,848.59 rows=30 width=312) (actual time=1,414.077..6,274.250 rows=2,209 loops=1)

4. 1.102 6,263.669 ↓ 74.0 2,221 1

Nested Loop (cost=202,581.62..420,846.76 rows=30 width=208) (actual time=1,414.051..6,263.669 rows=2,221 loops=1)

5. 2.066 6,258.125 ↓ 74.0 2,221 1

Nested Loop (cost=202,581.62..420,846.16 rows=30 width=210) (actual time=1,414.044..6,258.125 rows=2,221 loops=1)

6. 2.010 6,251.617 ↓ 74.0 2,221 1

Nested Loop (cost=202,581.62..420,845.56 rows=30 width=206) (actual time=1,414.035..6,251.617 rows=2,221 loops=1)

7. 0.896 6,227.397 ↓ 740.3 2,221 1

Nested Loop (cost=202,581.62..420,844.95 rows=3 width=174) (actual time=1,413.946..6,227.397 rows=2,221 loops=1)

8. 1.904 6,217.617 ↓ 740.3 2,221 1

Nested Loop (cost=202,581.61..420,844.89 rows=3 width=170) (actual time=1,413.934..6,217.617 rows=2,221 loops=1)

9. 1.678 6,206.829 ↓ 740.3 2,221 1

Nested Loop (cost=202,581.61..420,844.83 rows=3 width=166) (actual time=1,413.885..6,206.829 rows=2,221 loops=1)

10. 1,096.924 6,176.278 ↓ 740.3 2,221 1

Hash Join (cost=202,581.32..420,844.48 rows=3 width=158) (actual time=1,413.590..6,176.278 rows=2,221 loops=1)

  • Hash Cond: (("left"(f.house_id, 5))::integer = l_3.house_reg_id)
  • Join Filter: ((l.lobbyist_full_name % lc_full_name.lc_full_name) AND ((f.report_year = l_3.report_year) OR (abs((f.report_year - l_3.report_year)) < 4)))
  • Rows Removed by Join Filter: 36842
11. 1,023.147 4,884.685 ↑ 1.3 4,384,219 1

Hash Join (cost=185,885.51..361,201.96 rows=5,701,103 width=73) (actual time=1,202.080..4,884.685 rows=4,384,219 loops=1)

  • Hash Cond: (l.report_uuid = r.report_uuid)
12. 1,439.067 3,355.051 ↑ 1.2 4,681,312 1

Hash Join (cost=94,642.24..254,993.24 rows=5,701,103 width=91) (actual time=686.457..3,355.051 rows=4,681,312 loops=1)

  • Hash Cond: (l.report_uuid = f.report_uuid)
13. 363.238 1,234.105 ↓ 1.0 5,702,964 1

Append (cost=0.00..145,385.54 rows=5,701,103 width=35) (actual time=0.021..1,234.105 rows=5,702,964 loops=1)

14. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on lobbyists l (cost=0.00..0.00 rows=1 width=52) (actual time=0.007..0.007 rows=0 loops=1)

15. 729.698 729.698 ↓ 1.0 4,681,312 1

Seq Scan on lobbyists___after_2007 l_1 (cost=0.00..96,866.50 rows=4,679,450 width=35) (actual time=0.012..729.698 rows=4,681,312 loops=1)

16. 141.162 141.162 ↑ 1.0 1,021,652 1

Seq Scan on lobbyists___before_2008 l_2 (cost=0.00..20,013.52 rows=1,021,652 width=36) (actual time=0.017..141.162 rows=1,021,652 loops=1)

17. 295.048 681.879 ↑ 1.0 1,018,475 1

Hash (cost=81,900.44..81,900.44 rows=1,019,344 width=56) (actual time=681.879..681.879 rows=1,018,475 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 95940kB
18. 386.831 386.831 ↑ 1.0 1,018,475 1

Seq Scan on reports___fxml f (cost=0.00..81,900.44 rows=1,019,344 width=56) (actual time=0.014..386.831 rows=1,018,475 loops=1)

19. 313.723 506.487 ↑ 1.0 1,230,031 1

Hash (cost=75,847.01..75,847.01 rows=1,231,701 width=30) (actual time=506.486..506.487 rows=1,230,031 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 90859kB
20. 192.764 192.764 ↑ 1.0 1,230,031 1

Seq Scan on reports r (cost=0.00..75,847.01 rows=1,231,701 width=30) (actual time=15.645..192.764 rows=1,230,031 loops=1)

21. 0.008 194.669 ↑ 1.0 10 1

Hash (cost=16,695.68..16,695.68 rows=10 width=102) (actual time=194.669..194.669 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
22. 0.018 194.661 ↑ 1.0 10 1

Nested Loop (cost=16,693.71..16,695.68 rows=10 width=102) (actual time=194.625..194.661 rows=10 loops=1)

23. 57.770 194.593 ↑ 1.0 10 1

Limit (cost=16,693.71..16,695.38 rows=10 width=81) (actual time=194.577..194.593 rows=10 loops=1)

24. 136.823 136.823 ↑ 3.0 100,010 1

Index Scan using contributiondisclosure_pkey on ld203 l_3 (cost=0.42..50,796.10 rows=304,288 width=81) (actual time=0.041..136.823 rows=100,010 loops=1)

  • Filter: (filer_type = 'L'::bpchar)
  • Rows Removed by Filter: 39506
25. 0.050 0.050 ↑ 1.0 1 10

Function Scan on array_to_string lc_full_name (cost=0.00..0.01 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=10)

26. 28.873 28.873 ↑ 1.0 1 2,221

Function Scan on abs temporal_dist (cost=0.29..0.30 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=2,221)

27. 8.884 8.884 ↑ 1.0 1 2,221

Function Scan on similarity org_name_sim (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,221)

28. 8.884 8.884 ↑ 1.0 1 2,221

Function Scan on similarity full_name_sim (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,221)

29. 22.210 22.210 ↑ 10.0 1 2,221

Function Scan on regexp_matches lobbyist_full_name_parsed (cost=0.00..0.10 rows=10 width=32) (actual time=0.010..0.010 rows=1 loops=2,221)

30. 4.442 4.442 ↑ 1.0 1 2,221

Function Scan on similarity last_name_sim (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,221)

31. 4.442 4.442 ↑ 1.0 1 2,221

Function Scan on similarity first_name_sim (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,221)

32. 4.442 4.442 ↑ 1.0 1 2,221

Function Scan on total_score (cost=0.03..0.04 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,221)

  • Filter: (total_score > '0.6'::double precision)
  • Rows Removed by Filter: 0