explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wwR1

Settings
# exclusive inclusive rows x rows loops node
1. 0.070 8,297.489 ↑ 100.0 6 1

Nested Loop (cost=237,412.79..18,297,383.14 rows=600 width=71) (actual time=1,318.570..8,297.489 rows=6 loops=1)

2. 3.115 1,320.151 ↑ 100.0 6 1

Subquery Scan on sub2 (cost=237,412.79..237,471.29 rows=600 width=20) (actual time=1,286.397..1,320.151 rows=6 loops=1)

  • Filter: (sub2.rnk <= 2)
  • Rows Removed by Filter: 53959
3. 23.159 1,317.036 ↓ 30.0 53,965 1

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

4. 26.037 1,293.877 ↓ 30.0 53,965 1

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

  • Sort Key: sub.court_id, sub.case_count DESC
  • Sort Method: quicksort Memory: 5753kB
5. 5.633 1,267.840 ↓ 30.0 53,965 1

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

6. 17.944 1,262.207 ↓ 30.0 53,965 1

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

  • Group Key: c.court_id, ar_1.attorney_cluster_id
7. 35.466 1,244.263 ↓ 30.8 110,997 1

Gather Merge (cost=236,832.44..237,252.47 rows=3,600 width=20) (actual time=1,206.696..1,244.263 rows=110,997 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 31.057 1,208.797 ↓ 20.6 36,999 3 / 3

Sort (cost=235,832.41..235,836.91 rows=1,800 width=20) (actual time=1,201.548..1,208.797 rows=36,999 loops=3)

  • Sort Key: c.court_id, ar_1.attorney_cluster_id
  • Sort Method: quicksort Memory: 4440kB
  • Worker 0: Sort Method: quicksort Memory: 4431kB
  • Worker 1: Sort Method: quicksort Memory: 4410kB
9. 136.278 1,177.740 ↓ 20.6 36,999 3 / 3

Partial HashAggregate (cost=235,717.09..235,735.09 rows=1,800 width=20) (actual time=1,167.670..1,177.740 rows=36,999 loops=3)

  • Group Key: c.court_id, ar_1.attorney_cluster_id
10. 348.932 1,041.462 ↓ 1.8 316,211 3 / 3

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

  • Hash Cond: (ar_1.case_id = c.id)
11. 67.564 214.428 ↑ 1.3 842,161 3 / 3

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

12. 146.864 146.864 ↑ 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.013..146.864 rows=842,161 loops=3)

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

14. 36.077 478.102 ↑ 1.2 85,664 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 12128kB
15. 442.025 442.025 ↑ 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.124..442.025 rows=85,664 loops=3)

  • Filter: ((terminated_on IS NULL) AND (court_id = ANY ('{118,119,121}'::integer[])))
  • Rows Removed by Filter: 390355
16. 0.084 7.398 ↑ 2.0 1 6

Append (cost=0.00..7.77 rows=2 width=27) (actual time=1.230..1.233 rows=1 loops=6)

17. 0.018 0.018 ↓ 0.0 0 6

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

  • Filter: (sub2.attorney_cluster_id = id)
18. 7.296 7.296 ↑ 1.0 1 6

Index Scan using attorney_cache_20200202051415_pkey on attorney_cache_20200202051415 (cost=0.42..7.76 rows=1 width=27) (actual time=1.213..1.216 rows=1 loops=6)

  • Index Cond: (id = sub2.attorney_cluster_id)
19.          

SubPlan (for Nested Loop)

20. 0.690 6,969.870 ↑ 1,359.0 1 6

Unique (cost=30,088.67..30,092.07 rows=1,359 width=39) (actual time=1,161.199..1,161.645 rows=1 loops=6)

21. 15.306 6,969.180 ↓ 1.9 2,559 6

Sort (cost=30,088.67..30,092.07 rows=1,359 width=39) (actual time=1,161.194..1,161.530 rows=2,559 loops=6)

  • Sort Key: ucm.last_mentioned DESC
  • Sort Method: quicksort Memory: 684kB
22. 91.138 6,953.874 ↓ 1.9 2,559 6

Nested Loop (cost=0.56..30,017.95 rows=1,359 width=39) (actual time=12.598..1,158.979 rows=2,559 loops=6)

23. 20.562 708.252 ↓ 16.4 36,634 6

Append (cost=0.00..10,846.89 rows=2,237 width=16) (actual time=1.638..118.042 rows=36,634 loops=6)

24. 0.006 0.006 ↓ 0.0 0 6

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

  • Filter: ((NOT filter_out) AND (attorney_cluster_id = sub2.attorney_cluster_id))
25. 687.684 687.684 ↓ 16.4 36,634 6

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=1.630..114.614 rows=36,634 loops=6)

  • Index Cond: (attorney_cluster_id = sub2.attorney_cluster_id)
  • Filter: (NOT filter_out)
  • Rows Removed by Filter: 1618
26. 6,154.484 6,154.484 ↓ 0.0 0 219,803

Index Scan using ix_ucm_david on unnormalized_counsel_mention ucm (cost=0.56..8.57 rows=1 width=39) (actual time=0.028..0.028 rows=0 loops=219,803)

  • Index Cond: (id = ncm.id)
Planning time : 0.905 ms
Execution time : 8,302.241 ms