explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O7dZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 6,636.297 ↓ 3.5 106,095 1

Sort (cost=492,192.65..492,267.42 rows=29,910 width=4) (actual time=6,609.969..6,636.297 rows=106,095 loops=1)

  • Sort Key: (((SubPlan 1) - iplc_case.filed_on))
  • Sort Method: quicksort Memory: 8046kB
2. 833.726 6,708.938 ↓ 3.5 106,095 1

Gather (cost=43,776.45..489,969.09 rows=29,910 width=4) (actual time=485.837..6,708.938 rows=106,095 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 133.349 5,875.194 ↓ 2.8 35,365 3

Nested Loop Anti Join (cost=42,776.45..228,079.11 rows=12,462 width=8) (actual time=478.674..5,875.194 rows=35,365 loops=3)

4. 1,996.830 5,741.842 ↓ 2.9 37,325 3

Nested Loop Semi Join (cost=42,776.02..221,042.60 rows=12,835 width=8) (actual time=478.618..5,741.842 rows=37,325 loops=3)

5. 548.558 3,744.983 ↓ 2.9 67,125 3

Parallel Hash Semi Join (cost=42,775.58..204,246.19 rows=23,056 width=12) (actual time=478.380..3,744.983 rows=67,125 loops=3)

  • Hash Cond: (iplc_case.id = event.case_id)
  • Join Filter: (event.date > iplc_case.filed_on)
  • Rows Removed by Join Filter: 187
6. 2,719.489 2,719.489 ↑ 1.1 789,305 3

Parallel Seq Scan on iplc_case (cost=0.00..158,918.79 rows=852,450 width=8) (actual time=0.057..2,719.489 rows=789,305 loops=3)

  • Filter: ((terminated_on IS NULL) OR (terminated_on > '2009-01-01'::date))
  • Rows Removed by Filter: 70610
7. 44.688 476.936 ↑ 1.2 70,571 3

Parallel Hash (cost=41,689.61..41,689.61 rows=86,878 width=8) (actual time=476.936..476.936 rows=70,571 loops=3)

  • Buckets: 262144 Batches: 1 Memory Usage: 10368kB
8. 432.248 432.248 ↑ 1.2 70,571 3

Parallel Seq Scan on event (cost=0.00..41,689.61 rows=86,878 width=8) (actual time=0.121..432.248 rows=70,571 loops=3)

  • Filter: (event_type = 'summary_judgment'::text)
  • Rows Removed by Filter: 890227
9. 0.029 0.029 ↑ 1.0 1 201,374

Index Only Scan using positve_case_tags_pkey on positve_case_tags (cost=0.43..0.72 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=201,374)

  • Index Cond: (case_id = iplc_case.id)
  • Filter: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432}'::integer[]))
  • Rows Removed by Filter: 4
  • Heap Fetches: 171742
10. 0.003 0.003 ↓ 0.0 0 111,975

Index Only Scan using positve_case_tags_pkey on positve_case_tags positve_case_tags_1 (cost=0.43..0.55 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=111,975)

  • Index Cond: ((case_id = iplc_case.id) AND (tag_id = 359))
  • Heap Fetches: 3024
11.          

SubPlan (forGather)

12. 0.001 0.018 ↑ 1.0 1 106,095

GroupAggregate (cost=8.60..8.62 rows=1 width=5) (actual time=0.018..0.018 rows=1 loops=106,095)

  • Group Key: ((iplc_case.id = event_1.case_id))
13. 0.001 0.017 ↑ 1.0 1 106,095

Sort (cost=8.60..8.60 rows=1 width=5) (actual time=0.017..0.017 rows=1 loops=106,095)

  • Sort Key: ((iplc_case.id = event_1.case_id))
  • Sort Method: quicksort Memory: 25kB
14. 0.016 0.016 ↑ 1.0 1 106,095

Index Only Scan using event_pkey on event event_1 (cost=0.56..8.59 rows=1 width=5) (actual time=0.014..0.016 rows=1 loops=106,095)

  • Index Cond: ((case_id = iplc_case.id) AND (event_type = 'summary_judgment'::text))
  • Heap Fetches: 68711