explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2BpYo

Settings
# exclusive inclusive rows x rows loops node
1. 0.299 16,504.817 ↑ 20.0 30 1

Nested Loop (cost=237,412.79..18,297,383.14 rows=600 width=71) (actual time=1,278.255..16,504.817 rows=30 loops=1)

2. 3.199 1,278.078 ↑ 20.0 30 1

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

  • Filter: (sub2.rnk <= 10)
  • Rows Removed by Filter: 53935
3. 23.600 1,274.879 ↓ 30.0 53,965 1

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

4. 25.854 1,251.279 ↓ 30.0 53,965 1

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

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

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

6. 18.115 1,219.650 ↓ 30.0 53,965 1

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

  • Group Key: c.court_id, ar_1.attorney_cluster_id
7. 34.934 1,201.535 ↓ 30.8 111,038 1

Gather Merge (cost=236,832.44..237,252.47 rows=3,600 width=20) (actual time=1,163.777..1,201.535 rows=111,038 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 30.879 1,166.601 ↓ 20.6 37,013 3 / 3

Sort (cost=235,832.41..235,836.91 rows=1,800 width=20) (actual time=1,159.291..1,166.601 rows=37,013 loops=3)

  • Sort Key: c.court_id, ar_1.attorney_cluster_id
  • Sort Method: quicksort Memory: 4456kB
  • Worker 0: Sort Method: quicksort Memory: 4420kB
  • Worker 1: Sort Method: quicksort Memory: 4408kB
9. 132.076 1,135.722 ↓ 20.6 37,013 3 / 3

Partial HashAggregate (cost=235,717.09..235,735.09 rows=1,800 width=20) (actual time=1,125.824..1,135.722 rows=37,013 loops=3)

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

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

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

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

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

13. 0.001 0.001 ↓ 0.0 0 2 / 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=2)

14. 34.869 441.929 ↑ 1.2 85,664 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 12128kB
15. 407.060 407.060 ↑ 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.140..407.060 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.300 35.280 ↑ 2.0 1 30

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

17. 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)
18. 34.950 34.950 ↑ 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=1.163..1.165 rows=1 loops=30)

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

SubPlan (for Nested Loop)

20. 2.670 15,191.160 ↑ 1,359.0 1 30

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

21. 42.240 15,188.490 ↓ 1.3 1,822 30

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

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

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

23. 56.460 2,199.750 ↓ 9.2 20,517 30

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

24. 0.030 0.030 ↓ 0.0 0 30

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=30)

  • Filter: ((NOT filter_out) AND (attorney_cluster_id = sub2.attorney_cluster_id))
25. 2,143.260 2,143.260 ↓ 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=2.085..71.442 rows=20,517 loops=30)

  • Index Cond: (attorney_cluster_id = sub2.attorney_cluster_id)
  • Filter: (NOT filter_out)
  • Rows Removed by Filter: 1149
26. 12,925.752 12,925.752 ↓ 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.021..0.021 rows=0 loops=615,512)

  • Index Cond: (id = ncm.id)
Planning time : 1.156 ms
Execution time : 16,508.015 ms