explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UyEM

Settings
# exclusive inclusive rows x rows loops node
1. 0.115 5,262.563 ↑ 50.0 24 1

Nested Loop (cost=257,515.71..12,303,923.26 rows=1,200 width=62) (actual time=1,479.792..5,262.563 rows=24 loops=1)

2. 0.096 1,258.288 ↑ 20.0 30 1

Nested Loop (cost=237,412.79..242,142.79 rows=600 width=39) (actual time=1,222.473..1,258.288 rows=30 loops=1)

3. 3.164 1,257.262 ↑ 20.0 30 1

Subquery Scan on sub2 (cost=237,412.79..237,471.29 rows=600 width=20) (actual time=1,222.414..1,257.262 rows=30 loops=1)

  • Filter: (sub2.rnk <= 10)
  • Rows Removed by Filter: 53935
4. 23.925 1,254.098 ↓ 30.0 53,965 1

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

5. 27.951 1,230.173 ↓ 30.0 53,965 1

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

  • Sort Key: sub.court_id, sub.case_count DESC
  • Sort Method: quicksort Memory: 5753kB
6. 5.890 1,202.222 ↓ 30.0 53,965 1

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

7. 18.434 1,196.332 ↓ 30.0 53,965 1

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

  • Group Key: c.court_id, ar_1.attorney_cluster_id
8. 35.141 1,177.898 ↓ 30.8 110,978 1

Gather Merge (cost=236,832.44..237,252.47 rows=3,600 width=20) (actual time=1,139.544..1,177.898 rows=110,978 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 32.311 1,142.757 ↓ 20.6 36,993 3 / 3

Sort (cost=235,832.41..235,836.91 rows=1,800 width=20) (actual time=1,134.951..1,142.757 rows=36,993 loops=3)

  • Sort Key: c.court_id, ar_1.attorney_cluster_id
  • Sort Method: quicksort Memory: 4470kB
  • Worker 0: Sort Method: quicksort Memory: 4392kB
  • Worker 1: Sort Method: quicksort Memory: 4418kB
10. 134.478 1,110.446 ↓ 20.6 36,993 3 / 3

Partial HashAggregate (cost=235,717.09..235,735.09 rows=1,800 width=20) (actual time=1,100.204..1,110.446 rows=36,993 loops=3)

  • Group Key: c.court_id, ar_1.attorney_cluster_id
11. 345.958 975.968 ↓ 1.8 316,211 3 / 3

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

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

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

13. 141.470 141.470 ↑ 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.014..141.470 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. 35.103 420.879 ↑ 1.2 85,664 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 12160kB
16. 385.776 385.776 ↑ 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.079..385.776 rows=85,664 loops=3)

  • Filter: ((terminated_on IS NULL) AND (court_id = ANY ('{118,119,121}'::integer[])))
  • Rows Removed by Filter: 390355
17. 0.300 0.930 ↑ 2.0 1 30

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

18. 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)
19. 0.600 0.600 ↑ 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.018..0.020 rows=1 loops=30)

  • Index Cond: (id = sub2.attorney_cluster_id)
20. 1.590 4,004.160 ↑ 2.0 1 30

Unique (cost=20,102.92..20,102.93 rows=2 width=47) (actual time=133.351..133.472 rows=1 loops=30)

21. 32.970 4,002.570 ↓ 561.0 1,122 30

Sort (cost=20,102.92..20,102.93 rows=2 width=47) (actual time=133.346..133.419 rows=1,122 loops=30)

  • Sort Key: alf.last_seen DESC, ucm.last_mentioned DESC
  • Sort Method: quicksort Memory: 25kB
22. 118.884 3,969.600 ↓ 561.0 1,122 30

Nested Loop (cost=0.56..20,102.91 rows=2 width=47) (actual time=62.441..132.320 rows=1,122 loops=30)

23. 25.365 3,247.170 ↓ 2,235.3 6,706 30

Nested Loop (cost=0.00..20,077.20 rows=3 width=24) (actual time=56.023..108.239 rows=6,706 loops=30)

24. 0.480 2,543.400 ↓ 9.0 36 30

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

25. 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)
26. 2,542.920 2,542.920 ↓ 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=54.738..84.764 rows=36 loops=30)

  • Filter: (attorney_cluster_id = sub2.attorney_cluster_id)
  • Rows Removed by Filter: 974422
27. 22.365 678.405 ↓ 94.5 189 1,065

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

28. 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))
29. 656.040 656.040 ↓ 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.465..0.616 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
30. 603.546 603.546 ↓ 0.0 0 201,182

Index Scan using ix_ucm_david on unnormalized_counsel_mention ucm (cost=0.56..8.57 rows=1 width=39) (actual time=0.003..0.003 rows=0 loops=201,182)

  • Index Cond: (id = ncm.id)
Planning time : 1.241 ms
Execution time : 5,266.303 ms