explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iI48

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 69,796.182 ↑ 407,700.0 2 1

Nested Loop (cost=267,531.02..18,331,425.64 rows=815,400 width=62) (actual time=57,990.555..69,796.182 rows=2 loops=1)

2. 0.015 1,884.884 ↑ 200.0 3 1

Nested Loop (cost=237,412.79..242,142.79 rows=600 width=47) (actual time=1,846.819..1,884.884 rows=3 loops=1)

3. 3.589 1,883.168 ↑ 200.0 3 1

Subquery Scan on sub2 (cost=237,412.79..237,471.29 rows=600 width=28) (actual time=1,845.186..1,883.168 rows=3 loops=1)

  • Filter: (sub2.rnk <= 1)
  • Rows Removed by Filter: 53881
4. 26.143 1,879.579 ↓ 29.9 53,884 1

WindowAgg (cost=237,412.79..237,448.79 rows=1,800 width=28) (actual time=1,845.184..1,879.579 rows=53,884 loops=1)

5. 36.633 1,853.436 ↓ 29.9 53,884 1

Sort (cost=237,412.79..237,417.29 rows=1,800 width=20) (actual time=1,845.119..1,853.436 rows=53,884 loops=1)

  • Sort Key: sub.court_id, sub.case_count DESC
  • Sort Method: quicksort Memory: 5746kB
6. 6.724 1,816.803 ↓ 29.9 53,884 1

Subquery Scan on sub (cost=236,832.44..237,315.47 rows=1,800 width=20) (actual time=1,750.671..1,816.803 rows=53,884 loops=1)

7. 18.800 1,810.079 ↓ 29.9 53,884 1

Finalize GroupAggregate (cost=236,832.44..237,297.47 rows=1,800 width=20) (actual time=1,750.670..1,810.079 rows=53,884 loops=1)

  • Group Key: c.court_id, ar_1.attorney_cluster_id
8. 37.736 1,791.279 ↓ 30.8 110,778 1

Gather Merge (cost=236,832.44..237,252.47 rows=3,600 width=20) (actual time=1,750.658..1,791.279 rows=110,778 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 33.020 1,753.543 ↓ 20.5 36,926 3 / 3

Sort (cost=235,832.41..235,836.91 rows=1,800 width=20) (actual time=1,745.814..1,753.543 rows=36,926 loops=3)

  • Sort Key: c.court_id, ar_1.attorney_cluster_id
  • Sort Method: quicksort Memory: 4449kB
  • Worker 0: Sort Method: quicksort Memory: 4412kB
  • Worker 1: Sort Method: quicksort Memory: 4403kB
10. 150.550 1,720.523 ↓ 20.5 36,926 3 / 3

Partial HashAggregate (cost=235,717.09..235,735.09 rows=1,800 width=20) (actual time=1,709.886..1,720.523 rows=36,926 loops=3)

  • Group Key: c.court_id, ar_1.attorney_cluster_id
11. 376.255 1,569.973 ↓ 1.8 315,559 3 / 3

Parallel Hash Join (cost=194,808.01..234,416.89 rows=173,360 width=12) (actual time=896.277..1,569.973 rows=315,559 loops=3)

  • Hash Cond: (ar_1.case_id = c.id)
12. 72.086 298.177 ↑ 1.3 842,161 3 / 3

Parallel Append (cost=0.00..36,845.52 rows=1,052,702 width=12) (actual time=0.031..298.177 rows=842,161 loops=3)

13. 226.091 226.091 ↑ 1.2 842,161 3 / 3

Parallel Seq Scan on lex_attorney_representation_20200202051415 ar_1 (cost=0.00..31,582.01 rows=1,052,701 width=12) (actual time=0.030..226.091 rows=842,161 loops=3)

14. 0.000 0.000 ↓ 0.0 0 1 / 3

Parallel Seq Scan on lex_attorney_representation ar (cost=0.00..0.00 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)

15. 39.592 895.541 ↑ 1.2 85,875 3 / 3

Parallel Hash (cost=193,530.34..193,530.34 rows=102,214 width=8) (actual time=895.541..895.541 rows=85,875 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 12192kB
16. 855.949 855.949 ↑ 1.2 85,875 3 / 3

Parallel Seq Scan on lex_case c (cost=0.00..193,530.34 rows=102,214 width=8) (actual time=0.069..855.949 rows=85,875 loops=3)

  • Filter: ((terminated_on IS NULL) AND (court_id = ANY ('{118,119,121}'::integer[])))
  • Rows Removed by Filter: 390726
17. 0.024 1.701 ↑ 2.0 1 3

Append (cost=0.00..7.77 rows=2 width=27) (actual time=0.565..0.567 rows=1 loops=3)

18. 0.009 0.009 ↓ 0.0 0 3

Seq Scan on attorney_cache (cost=0.00..0.00 rows=1 width=40) (actual time=0.003..0.003 rows=0 loops=3)

  • Filter: (sub2.attorney_cluster_id = id)
19. 1.668 1.668 ↑ 1.0 1 3

Index Scan using attorney_cache_20200202051415_pkey on attorney_cache_20200202051415 (cost=0.42..7.76 rows=1 width=27) (actual time=0.554..0.556 rows=1 loops=3)

  • Index Cond: (id = sub2.attorney_cluster_id)
20. 0.036 67,911.285 ↑ 1,359.0 1 3

Unique (cost=30,118.23..30,121.62 rows=1,359 width=39) (actual time=22,637.066..22,637.095 rows=1 loops=3)

21. 1.596 67,911.249 ↑ 7.2 190 3

Sort (cost=30,118.23..30,121.62 rows=1,359 width=39) (actual time=22,637.063..22,637.083 rows=190 loops=3)

  • Sort Key: ucm.last_mentioned DESC
  • Sort Method: quicksort Memory: 60kB
22. 0.729 67,909.653 ↑ 7.2 190 3

Result (cost=0.57..30,047.50 rows=1,359 width=39) (actual time=893.012..22,636.551 rows=190 loops=3)

  • One-Time Filter: (sub2.rnk <= 1)
23. 116.550 67,908.924 ↑ 7.2 190 3

Nested Loop (cost=0.57..30,047.50 rows=1,359 width=39) (actual time=893.009..22,636.308 rows=190 loops=3)

24. 29.355 1,558.674 ↓ 17.8 39,780 3

Append (cost=0.00..10,846.89 rows=2,237 width=16) (actual time=2.659..519.558 rows=39,780 loops=3)

25. 0.006 0.006 ↓ 0.0 0 3

Seq Scan on normalized_counsel_mention ncm (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=3)

  • Filter: ((NOT filter_out) AND (attorney_cluster_id = sub2.attorney_cluster_id))
26. 1,529.313 1,529.313 ↓ 17.8 39,780 3

Index Scan using normalized_counsel_mention_202002020514_attorney_cluster_id_idx on normalized_counsel_mention_20200202051415 ncm_1 (cost=0.57..10,835.71 rows=2,236 width=16) (actual time=2.652..509.771 rows=39,780 loops=3)

  • Index Cond: (attorney_cluster_id = sub2.attorney_cluster_id)
  • Filter: (NOT filter_out)
  • Rows Removed by Filter: 1485
27. 66,233.700 66,233.700 ↓ 0.0 0 119,340

Index Scan using unnormalized_counsel_mention_pkey on unnormalized_counsel_mention ucm (cost=0.57..8.57 rows=1 width=39) (actual time=0.555..0.555 rows=0 loops=119,340)

  • Index Cond: (id = ncm.id)
  • Filter: (email <> ''::text)
  • Rows Removed by Filter: 1
Planning time : 1.362 ms
Execution time : 69,799.957 ms