explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ooN

Settings
# exclusive inclusive rows x rows loops node
1. 0.156 5,262.737 ↑ 3.2 28 1

Nested Loop (cost=257,516.96..841,560.18 rows=90 width=129) (actual time=1,320.758..5,262.737 rows=28 loops=1)

2. 0.116 5,261.657 ↑ 3.2 28 1

Nested Loop (cost=257,516.96..840,798.10 rows=90 width=106) (actual time=1,320.723..5,261.657 rows=28 loops=1)

3. 0.086 1,267.041 ↑ 1.0 30 1

Nested Loop (cost=237,414.02..237,707.70 rows=30 width=75) (actual time=1,232.313..1,267.041 rows=30 loops=1)

4. 0.143 1,266.115 ↑ 1.0 30 1

Hash Join (cost=237,414.02..237,474.12 rows=30 width=56) (actual time=1,232.255..1,266.115 rows=30 loops=1)

  • Hash Cond: (sub2.court_id = ct.id)
5. 3.164 1,265.950 ↑ 20.0 30 1

Subquery Scan on sub2 (cost=237,412.79..237,471.29 rows=600 width=28) (actual time=1,232.223..1,265.950 rows=30 loops=1)

  • Filter: (sub2.rnk <= 10)
  • Rows Removed by Filter: 53935
6. 23.251 1,262.786 ↓ 30.0 53,965 1

WindowAgg (cost=237,412.79..237,448.79 rows=1,800 width=28) (actual time=1,232.221..1,262.786 rows=53,965 loops=1)

7. 25.604 1,239.535 ↓ 30.0 53,965 1

Sort (cost=237,412.79..237,417.29 rows=1,800 width=20) (actual time=1,232.199..1,239.535 rows=53,965 loops=1)

  • Sort Key: sub.court_id, sub.case_count DESC
  • Sort Method: quicksort Memory: 5753kB
8. 6.005 1,213.931 ↓ 30.0 53,965 1

Subquery Scan on sub (cost=236,832.44..237,315.47 rows=1,800 width=20) (actual time=1,153.540..1,213.931 rows=53,965 loops=1)

9. 18.138 1,207.926 ↓ 30.0 53,965 1

Finalize GroupAggregate (cost=236,832.44..237,297.47 rows=1,800 width=20) (actual time=1,153.538..1,207.926 rows=53,965 loops=1)

  • Group Key: c.court_id, ar_1.attorney_cluster_id
10. 34.498 1,189.788 ↓ 30.8 110,765 1

Gather Merge (cost=236,832.44..237,252.47 rows=3,600 width=20) (actual time=1,153.524..1,189.788 rows=110,765 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 29.742 1,155.290 ↓ 20.5 36,922 3 / 3

Sort (cost=235,832.41..235,836.91 rows=1,800 width=20) (actual time=1,148.451..1,155.290 rows=36,922 loops=3)

  • Sort Key: c.court_id, ar_1.attorney_cluster_id
  • Sort Method: quicksort Memory: 4466kB
  • Worker 0: Sort Method: quicksort Memory: 4391kB
  • Worker 1: Sort Method: quicksort Memory: 4406kB
12. 126.792 1,125.548 ↓ 20.5 36,922 3 / 3

Partial HashAggregate (cost=235,717.09..235,735.09 rows=1,800 width=20) (actual time=1,115.997..1,125.548 rows=36,922 loops=3)

  • Group Key: c.court_id, ar_1.attorney_cluster_id
13. 330.686 998.756 ↓ 1.8 316,211 3 / 3

Parallel Hash Join (cost=194,808.01..234,416.89 rows=173,360 width=12) (actual time=459.573..998.756 rows=316,211 loops=3)

  • Hash Cond: (ar_1.case_id = c.id)
14. 66.621 209.221 ↑ 1.3 842,161 3 / 3

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

15. 142.600 142.600 ↑ 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.012..142.600 rows=842,161 loops=3)

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

17. 34.699 458.849 ↑ 1.2 85,664 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 12160kB
18. 424.150 424.150 ↑ 1.2 85,664 3 / 3

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

  • Filter: ((terminated_on IS NULL) AND (court_id = ANY ('{118,119,121}'::integer[])))
  • Rows Removed by Filter: 390355
19. 0.005 0.022 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=36) (actual time=0.022..0.022 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.017 0.017 ↑ 1.0 10 1

Seq Scan on lex_court ct (cost=0.00..1.10 rows=10 width=36) (actual time=0.014..0.017 rows=10 loops=1)

21. 0.240 0.840 ↑ 2.0 1 30

Append (cost=0.00..7.77 rows=2 width=27) (actual time=0.025..0.028 rows=1 loops=30)

22. 0.030 0.030 ↓ 0.0 0 30

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

  • Filter: (sub2.attorney_cluster_id = id)
23. 0.570 0.570 ↑ 1.0 1 30

Index Scan using attorney_cache_20200202051415_pkey on attorney_cache_20200202051415 (cost=0.42..7.76 rows=1 width=27) (actual time=0.016..0.019 rows=1 loops=30)

  • Index Cond: (id = sub2.attorney_cluster_id)
24. 8.730 3,994.500 ↑ 3.0 1 30

Unique (cost=20,102.95..20,102.95 rows=3 width=56) (actual time=132.177..133.150 rows=1 loops=30)

25. 249.360 3,985.770 ↓ 2,235.3 6,706 30

Sort (cost=20,102.95..20,102.95 rows=3 width=56) (actual time=132.169..132.859 rows=6,706 loops=30)

  • Sort Key: alf.last_seen DESC, ((ucm.email <> ''::text)), ucm.last_mentioned DESC
  • Sort Method: quicksort Memory: 25kB
26. 82.520 3,736.410 ↓ 2,235.3 6,706 30

Nested Loop (cost=0.57..20,102.92 rows=3 width=56) (actual time=53.977..124.547 rows=6,706 loops=30)

27. 24.690 2,647.980 ↓ 2,235.3 6,706 30

Nested Loop (cost=0.00..20,077.20 rows=3 width=32) (actual time=53.956..88.266 rows=6,706 loops=30)

28. 0.420 2,499.750 ↓ 9.0 36 30

Append (cost=0.00..20,042.73 rows=4 width=24) (actual time=53.909..83.325 rows=36 loops=30)

29. 0.000 0.000 ↓ 0.0 0 30

Seq Scan on attorney_law_firm alf (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=30)

  • Filter: (attorney_cluster_id = sub2.attorney_cluster_id)
30. 2,499.330 2,499.330 ↓ 12.0 36 30

Seq Scan on attorney_law_firm_20200202051415 alf_1 (cost=0.00..20,042.71 rows=3 width=24) (actual time=53.902..83.311 rows=36 loops=30)

  • Filter: (attorney_cluster_id = sub2.attorney_cluster_id)
  • Rows Removed by Filter: 974422
31. 20.235 123.540 ↓ 94.5 189 1,065

Append (cost=0.00..8.60 rows=2 width=24) (actual time=0.011..0.116 rows=189 loops=1,065)

32. 0.000 0.000 ↓ 0.0 0 1,065

Seq Scan on normalized_counsel_mention ncm (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=1,065)

  • Filter: ((NOT filter_out) AND (attorney_cluster_id = sub2.attorney_cluster_id) AND (alf.law_firm_cluster_id = law_firm_cluster_id))
33. 103.305 103.305 ↓ 189.0 189 1,065

Index Scan using normalized_counsel_mention_20200202051415_cluster_idx on normalized_counsel_mention_20200202051415 ncm_1 (cost=0.57..8.59 rows=1 width=24) (actual time=0.009..0.097 rows=189 loops=1,065)

  • Index Cond: ((law_firm_cluster_id = alf.law_firm_cluster_id) AND (attorney_cluster_id = sub2.attorney_cluster_id))
  • Filter: (NOT filter_out)
  • Rows Removed by Filter: 15
34. 1,005.910 1,005.910 ↑ 1.0 1 201,182

Index Scan using unnormalized_counsel_mention_pkey on unnormalized_counsel_mention ucm (cost=0.57..8.57 rows=1 width=39) (actual time=0.005..0.005 rows=1 loops=201,182)

  • Index Cond: (id = ncm.id)
35. 0.308 0.924 ↑ 2.0 1 28

Append (cost=0.00..8.45 rows=2 width=31) (actual time=0.032..0.033 rows=1 loops=28)

36. 0.028 0.028 ↓ 0.0 0 28

Seq Scan on law_firm_cache (cost=0.00..0.00 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=28)

  • Filter: (alf.law_firm_cluster_id = id)
37. 0.588 0.588 ↑ 1.0 1 28

Index Scan using law_firm_cache_20200202051415_pkey on law_firm_cache_20200202051415 (cost=0.42..8.44 rows=1 width=31) (actual time=0.020..0.021 rows=1 loops=28)

  • Index Cond: (id = alf.law_firm_cluster_id)
Planning time : 1.379 ms
Execution time : 5,266.672 ms