explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ujc5

Settings
# exclusive inclusive rows x rows loops node
1. 2.864 15,195.907 ↑ 1.0 1,644 1

Sort (cost=903,696.72..903,700.83 rows=1,644 width=359) (actual time=15,195.738..15,195.907 rows=1,644 loops=1)

  • Sort Key: (COALESCE((sum((count(DISTINCT iplc_case.id)))), '0'::numeric)) DESC
  • Sort Method: quicksort Memory: 912kB
2. 1.576 15,193.043 ↑ 1.0 1,644 1

Hash Left Join (cost=884,564.95..903,608.90 rows=1,644 width=359) (actual time=14,567.936..15,193.043 rows=1,644 loops=1)

  • Hash Cond: (fjc_judge.court_id = court_1.id)
3. 1.760 15,191.373 ↑ 1.0 1,644 1

Hash Right Join (cost=884,557.52..903,596.99 rows=1,644 width=172) (actual time=14,567.831..15,191.373 rows=1,644 loops=1)

  • Hash Cond: (fjc_judge_1.id = fjc_judge.id)
4. 5.433 15,188.277 ↑ 1.3 1,293 1

GroupAggregate (cost=884,465.53..903,484.24 rows=1,644 width=100) (actual time=14,566.478..15,188.277 rows=1,293 loops=1)

  • Group Key: fjc_judge_1.id
5. 2.920 15,182.844 ↑ 4.1 11,503 1

Merge Join (cost=884,465.53..902,978.71 rows=47,676 width=20) (actual time=14,566.353..15,182.844 rows=11,503 loops=1)

  • Merge Cond: (fjc_judge_2.id = fjc_judge_1.id)
6. 404.787 15,178.269 ↑ 4.1 11,503 1

GroupAggregate (cost=884,465.25..901,824.95 rows=47,676 width=20) (actual time=14,565.475..15,178.269 rows=11,503 loops=1)

  • Group Key: fjc_judge_2.id, (date_part('year'::text, (iplc_case.filed_on)::timestamp without time zone))
7. 1,626.383 14,773.482 ↑ 1.1 1,505,520 1

Sort (cost=884,465.25..888,626.39 rows=1,664,456 width=16) (actual time=14,565.422..14,773.482 rows=1,505,520 loops=1)

  • Sort Key: fjc_judge_2.id, (date_part('year'::text, (iplc_case.filed_on)::timestamp without time zone))
  • Sort Method: external merge Disk: 44200kB
8. 170.670 13,147.099 ↑ 1.1 1,505,520 1

Gather (cost=435,617.73..712,471.86 rows=1,664,456 width=16) (actual time=8,215.405..13,147.099 rows=1,505,520 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
9. 624.895 12,976.429 ↓ 2.2 1,505,520 1

Hash Join (cost=434,617.73..545,026.26 rows=693,523 width=16) (actual time=8,214.818..12,976.429 rows=1,505,520 loops=1)

  • Hash Cond: (fjc_judge_case_assignment.fjc_judge_id = fjc_judge_2.id)
10. 2,868.355 12,351.090 ↓ 3.8 2,647,585 1

Parallel Hash Join (cost=434,525.74..539,643.44 rows=693,523 width=12) (actual time=8,214.356..12,351.090 rows=2,647,585 loops=1)

  • Hash Cond: (fjc_judge_case_assignment.case_id = iplc_case.id)
11. 1,275.039 1,275.039 ↓ 2.4 5,212,062 1

Parallel Seq Scan on fjc_judge_case_assignment (cost=0.00..94,058.74 rows=2,178,474 width=8) (actual time=0.012..1,275.039 rows=5,212,062 loops=1)

12. 832.279 8,207.696 ↓ 3.7 1,432,450 1

Parallel Hash (cost=429,687.21..429,687.21 rows=387,082 width=12) (actual time=8,207.696..8,207.696 rows=1,432,450 loops=1)

  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 91808kB
13. 896.668 7,375.417 ↓ 3.7 1,432,450 1

Parallel Hash Anti Join (cost=224,100.05..429,687.21 rows=387,082 width=12) (actual time=2,581.358..7,375.417 rows=1,432,450 loops=1)

  • Hash Cond: (iplc_case.id = positve_case_tags_1.case_id)
14. 1,716.202 5,917.695 ↓ 3.7 1,615,251 1

Parallel Hash Semi Join (cost=148,709.47..349,224.06 rows=436,592 width=12) (actual time=2,016.901..5,917.695 rows=1,615,251 loops=1)

  • Hash Cond: (iplc_case.id = positve_case_tags.case_id)
15. 2,211.424 2,211.424 ↓ 2.4 2,258,794 1

Parallel Seq Scan on iplc_case (cost=0.00..193,189.09 rows=940,345 width=8) (actual time=0.026..2,211.424 rows=2,258,794 loops=1)

  • Filter: (date_part('year'::text, (filed_on)::timestamp without time zone) >= '2010'::double precision)
  • Rows Removed by Filter: 663669
16. 900.126 1,990.069 ↓ 2.2 2,041,973 1

Parallel Hash (cost=137,167.59..137,167.59 rows=923,351 width=4) (actual time=1,990.069..1,990.069 rows=2,041,973 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 112672kB
17. 199.052 1,089.943 ↓ 2.2 2,041,973 1

Parallel Append (cost=0.00..137,167.59 rows=923,351 width=4) (actual time=191.548..1,089.943 rows=2,041,973 loops=1)

18. 722.341 890.889 ↓ 2.2 2,041,973 1

Parallel Bitmap Heap Scan on positve_case_tags (cost=39,789.33..132,550.83 rows=923,350 width=4) (actual time=191.544..890.889 rows=2,041,973 loops=1)

  • Recheck Cond: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432,451,450,452,449}'::integer[]))
  • Heap Blocks: exact=58264
19. 168.548 168.548 ↑ 1.0 2,136,879 1

Bitmap Index Scan on ix_positve_case_tags_tag_id (cost=0.00..39,235.32 rows=2,216,040 width=0) (actual time=168.548..168.548 rows=2,136,879 loops=1)

  • Index Cond: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432,451,450,452,449}'::integer[]))
20. 0.002 0.002 ↓ 0.0 0 1

Parallel Seq Scan on positive_case_tags (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432,451,450,452,449}'::integer[]))
21. 86.212 561.054 ↓ 1.7 238,480 1

Parallel Hash (cost=73,667.03..73,667.03 rows=137,884 width=4) (actual time=561.053..561.054 rows=238,480 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 13440kB
22. 23.019 474.842 ↓ 1.7 238,480 1

Parallel Append (cost=0.00..73,667.03 rows=137,884 width=4) (actual time=165.462..474.842 rows=238,480 loops=1)

23. 297.995 451.821 ↓ 1.7 238,480 1

Parallel Bitmap Heap Scan on positve_case_tags positve_case_tags_1 (cost=6,193.06..72,977.61 rows=137,883 width=4) (actual time=165.459..451.821 rows=238,480 loops=1)

  • Recheck Cond: (tag_id = 359)
  • Heap Blocks: exact=39975
24. 153.826 153.826 ↓ 1.0 339,610 1

Bitmap Index Scan on ix_positve_case_tags_tag_id (cost=0.00..6,110.34 rows=330,920 width=0) (actual time=153.826..153.826 rows=339,610 loops=1)

  • Index Cond: (tag_id = 359)
25. 0.002 0.002 ↓ 0.0 0 1

Parallel Seq Scan on positive_case_tags positive_case_tags_1 (cost=0.00..0.00 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (tag_id = 359)
26. 0.196 0.444 ↑ 1.0 1,644 1

Hash (cost=71.44..71.44 rows=1,644 width=4) (actual time=0.444..0.444 rows=1,644 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
27. 0.248 0.248 ↑ 1.0 1,644 1

Seq Scan on fjc_judge fjc_judge_2 (cost=0.00..71.44 rows=1,644 width=4) (actual time=0.007..0.248 rows=1,644 loops=1)

28. 1.655 1.655 ↑ 1.0 1,644 1

Index Only Scan using fjc_judge_pkey on fjc_judge fjc_judge_1 (cost=0.28..76.94 rows=1,644 width=4) (actual time=0.872..1.655 rows=1,644 loops=1)

  • Heap Fetches: 0
29. 0.728 1.336 ↑ 1.0 1,644 1

Hash (cost=71.44..71.44 rows=1,644 width=76) (actual time=1.336..1.336 rows=1,644 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 187kB
30. 0.608 0.608 ↑ 1.0 1,644 1

Seq Scan on fjc_judge (cost=0.00..71.44 rows=1,644 width=76) (actual time=0.010..0.608 rows=1,644 loops=1)

31. 0.040 0.094 ↑ 1.0 108 1

Hash (cost=6.08..6.08 rows=108 width=187) (actual time=0.094..0.094 rows=108 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
32. 0.054 0.054 ↑ 1.0 108 1

Seq Scan on court court_1 (cost=0.00..6.08 rows=108 width=187) (actual time=0.010..0.054 rows=108 loops=1)

Planning time : 3.943 ms
Execution time : 15,233.947 ms