explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jp7S

Settings
# exclusive inclusive rows x rows loops node
1. 4.027 47,831.913 ↑ 1.0 1,644 1

Sort (cost=903,709.91..903,714.02 rows=1,644 width=359) (actual time=47,831.650..47,831.913 rows=1,644 loops=1)

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

Hash Left Join (cost=884,577.68..903,622.10 rows=1,644 width=359) (actual time=47,081.969..47,827.886 rows=1,644 loops=1)

  • Hash Cond: (fjc_judge.court_id = court_1.id)
3. 2.229 47,825.621 ↑ 1.0 1,644 1

Hash Right Join (cost=884,570.25..903,610.19 rows=1,644 width=172) (actual time=47,081.804..47,825.621 rows=1,644 loops=1)

  • Hash Cond: (fjc_judge_1.id = fjc_judge.id)
4. 6.846 47,822.298 ↑ 1.3 1,293 1

GroupAggregate (cost=884,478.26..903,497.43 rows=1,644 width=100) (actual time=47,080.697..47,822.298 rows=1,293 loops=1)

  • Group Key: fjc_judge_1.id
5. 3.801 47,815.452 ↑ 4.1 11,503 1

Merge Join (cost=884,478.26..902,991.90 rows=47,676 width=20) (actual time=47,080.452..47,815.452 rows=11,503 loops=1)

  • Merge Cond: (fjc_judge_2.id = fjc_judge_1.id)
6. 476.013 47,809.683 ↑ 4.1 11,503 1

GroupAggregate (cost=884,477.98..901,838.14 rows=47,676 width=20) (actual time=47,079.526..47,809.683 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,730.086 47,333.670 ↑ 1.1 1,505,538 1

Sort (cost=884,477.98..888,639.24 rows=1,664,502 width=16) (actual time=47,079.458..47,333.670 rows=1,505,538 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. 204.635 45,603.584 ↑ 1.1 1,505,538 1

Gather (cost=435,617.73..712,479.51 rows=1,664,502 width=16) (actual time=34,587.231..45,603.584 rows=1,505,538 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
9. 760.609 45,398.949 ↓ 2.2 1,505,538 1

Hash Join (cost=434,617.73..545,029.31 rows=693,542 width=16) (actual time=34,586.705..45,398.949 rows=1,505,538 loops=1)

  • Hash Cond: (fjc_judge_case_assignment.fjc_judge_id = fjc_judge_2.id)
10. 3,537.202 44,637.788 ↓ 3.8 2,647,602 1

Parallel Hash Join (cost=434,525.74..539,646.34 rows=693,542 width=12) (actual time=34,586.137..44,637.788 rows=2,647,602 loops=1)

  • Hash Cond: (fjc_judge_case_assignment.case_id = iplc_case.id)
11. 6,521.234 6,521.234 ↓ 2.4 5,212,101 1

Parallel Seq Scan on fjc_judge_case_assignment (cost=0.00..94,061.34 rows=2,178,534 width=8) (actual time=0.012..6,521.234 rows=5,212,101 loops=1)

12. 890.866 34,579.352 ↓ 3.7 1,432,450 1

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

  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 91808kB
13. 1,006.631 33,688.486 ↓ 3.7 1,432,450 1

Parallel Hash Anti Join (cost=224,100.05..429,687.21 rows=387,082 width=12) (actual time=17,024.481..33,688.486 rows=1,432,450 loops=1)

  • Hash Cond: (iplc_case.id = positve_case_tags_1.case_id)
14. 1,901.211 28,030.554 ↓ 3.7 1,615,251 1

Parallel Hash Semi Join (cost=148,709.47..349,224.06 rows=436,592 width=12) (actual time=12,369.932..28,030.554 rows=1,615,251 loops=1)

  • Hash Cond: (iplc_case.id = positve_case_tags.case_id)
15. 13,789.234 13,789.234 ↓ 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.014..13,789.234 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. 983.509 12,340.109 ↓ 2.2 2,041,966 1

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

  • Buckets: 4194304 Batches: 1 Memory Usage: 112672kB
17. 180.337 11,356.600 ↓ 2.2 2,041,966 1

Parallel Append (cost=0.00..137,167.59 rows=923,351 width=4) (actual time=614.001..11,356.600 rows=2,041,966 loops=1)

18. 10,581.445 11,176.262 ↓ 2.2 2,041,966 1

Parallel Bitmap Heap Scan on positve_case_tags (cost=39,789.33..132,550.83 rows=923,350 width=4) (actual time=613.997..11,176.262 rows=2,041,966 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. 594.817 594.817 ↑ 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=594.817..594.817 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.001 0.001 ↓ 0.0 0 1

Parallel Seq Scan on positive_case_tags (cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 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. 111.213 4,651.301 ↓ 1.7 238,480 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 13440kB
22. 21.436 4,540.088 ↓ 1.7 238,480 1

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

23. 4,374.583 4,518.651 ↓ 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=154.144..4,518.651 rows=238,480 loops=1)

  • Recheck Cond: (tag_id = 359)
  • Heap Blocks: exact=39975
24. 144.068 144.068 ↓ 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=144.068..144.068 rows=339,610 loops=1)

  • Index Cond: (tag_id = 359)
25. 0.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

  • Filter: (tag_id = 359)
26. 0.231 0.552 ↑ 1.0 1,644 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 74kB
27. 0.321 0.321 ↑ 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.008..0.321 rows=1,644 loops=1)

28. 1.968 1.968 ↑ 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.919..1.968 rows=1,644 loops=1)

  • Heap Fetches: 0
29. 0.520 1.094 ↑ 1.0 1,644 1

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

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

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

31. 0.042 0.142 ↑ 1.0 108 1

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

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

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

Planning time : 4.618 ms
Execution time : 47,879.485 ms