explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ejFm

Settings
# exclusive inclusive rows x rows loops node
1. 0.065 3,205.624 ↑ 31,361.5 26 1

Nested Loop (cost=267,501.46..18,313,691.14 rows=815,400 width=62) (actual time=1,261.549..3,205.624 rows=26 loops=1)

2. 0.115 1,241.009 ↑ 20.0 30 1

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

3. 3.231 1,239.934 ↑ 20.0 30 1

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

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

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

5. 25.754 1,213.460 ↓ 30.0 53,965 1

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

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

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

7. 17.209 1,181.768 ↓ 30.0 53,965 1

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

  • Group Key: c.court_id, ar_1.attorney_cluster_id
8. 35.341 1,164.559 ↓ 30.8 110,883 1

Gather Merge (cost=236,832.44..237,252.47 rows=3,600 width=20) (actual time=1,127.015..1,164.559 rows=110,883 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 30.964 1,129.218 ↓ 20.5 36,961 3 / 3

Sort (cost=235,832.41..235,836.91 rows=1,800 width=20) (actual time=1,121.827..1,129.218 rows=36,961 loops=3)

  • Sort Key: c.court_id, ar_1.attorney_cluster_id
  • Sort Method: quicksort Memory: 4455kB
  • Worker 0: Sort Method: quicksort Memory: 4406kB
  • Worker 1: Sort Method: quicksort Memory: 4410kB
10. 133.568 1,098.254 ↓ 20.5 36,961 3 / 3

Partial HashAggregate (cost=235,717.09..235,735.09 rows=1,800 width=20) (actual time=1,088.262..1,098.254 rows=36,961 loops=3)

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

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

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

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

13. 138.831 138.831 ↑ 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..138.831 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.114 415.592 ↑ 1.2 85,664 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 12128kB
16. 380.478 380.478 ↑ 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.158..380.478 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.390 0.960 ↑ 2.0 1 30

Append (cost=0.00..7.77 rows=2 width=27) (actual time=0.029..0.032 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.540 0.540 ↑ 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.015..0.018 rows=1 loops=30)

  • Index Cond: (id = sub2.attorney_cluster_id)
20. 2.460 1,964.550 ↑ 1,359.0 1 30

Unique (cost=30,088.67..30,092.07 rows=1,359 width=39) (actual time=65.170..65.485 rows=1 loops=30)

21. 25.050 1,962.090 ↓ 1.3 1,822 30

Sort (cost=30,088.67..30,092.07 rows=1,359 width=39) (actual time=65.164..65.403 rows=1,822 loops=30)

  • Sort Key: ucm.last_mentioned DESC
  • Sort Method: quicksort Memory: 176kB
22. 269.666 1,937.040 ↓ 1.3 1,822 30

Nested Loop (cost=0.56..30,017.95 rows=1,359 width=39) (actual time=5.230..64.568 rows=1,822 loops=30)

23. 49.680 436.350 ↓ 9.2 20,517 30

Append (cost=0.00..10,846.89 rows=2,237 width=16) (actual time=0.024..14.545 rows=20,517 loops=30)

24. 0.000 0.000 ↓ 0.0 0 30

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

  • Filter: ((NOT filter_out) AND (attorney_cluster_id = sub2.attorney_cluster_id))
25. 386.670 386.670 ↓ 9.2 20,517 30

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=0.019..12.889 rows=20,517 loops=30)

  • Index Cond: (attorney_cluster_id = sub2.attorney_cluster_id)
  • Filter: (NOT filter_out)
  • Rows Removed by Filter: 1149
26. 1,231.024 1,231.024 ↓ 0.0 0 615,512

Index Scan using ix_ucm_david on unnormalized_counsel_mention ucm (cost=0.56..8.57 rows=1 width=39) (actual time=0.002..0.002 rows=0 loops=615,512)

  • Index Cond: (id = ncm.id)
Planning time : 0.933 ms
Execution time : 3,208.904 ms