explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2sGS

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

Sort (cost=498,320.60..498,396.27 rows=30,267 width=4) (actual time=5,191.333..5,218.868 rows=106,095 loops=1)

  • Sort Key: (((SubPlan 1) - iplc_case.filed_on))
  • Sort Method: quicksort Memory: 8046kB
2. 909.337 5,341.222 ↓ 3.5 106,095 1

Gather (cost=43,795.89..496,067.91 rows=30,267 width=4) (actual time=434.963..5,341.222 rows=106,095 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
3. 135.230 4,431.872 ↓ 4.2 53,048 2

Nested Loop Anti Join (cost=42,795.89..231,064.00 rows=12,611 width=8) (actual time=430.825..4,431.872 rows=53,048 loops=2)

4. 1,362.031 4,296.640 ↓ 4.3 55,988 2

Nested Loop Semi Join (cost=42,795.45..223,943.61 rows=12,988 width=8) (actual time=430.811..4,296.640 rows=55,988 loops=2)

5. 742.676 2,934.596 ↓ 4.3 100,687 2

Parallel Hash Semi Join (cost=42,795.02..206,946.14 rows=23,332 width=12) (actual time=430.714..2,934.596 rows=100,687 loops=2)

  • Hash Cond: (iplc_case.id = event.case_id)
  • Join Filter: (event.date > iplc_case.filed_on)
  • Rows Removed by Join Filter: 280
6. 1,762.996 1,762.996 ↓ 1.4 1,183,941 2

Parallel Seq Scan on iplc_case (cost=0.00..161,595.54 rows=852,450 width=8) (actual time=0.059..1,762.996 rows=1,183,941 loops=2)

  • Filter: ((filed_on < '2019-04-09'::date) AND ((terminated_on IS NULL) OR (terminated_on > '2009-01-01'::date)))
  • Rows Removed by Filter: 105932
7. 50.494 428.924 ↓ 1.2 105,856 2

Parallel Hash (cost=41,696.06..41,696.06 rows=87,917 width=8) (actual time=428.924..428.924 rows=105,856 loops=2)

  • Buckets: 262144 Batches: 1 Memory Usage: 10336kB
8. 378.430 378.430 ↓ 1.2 105,856 2

Parallel Seq Scan on event (cost=0.00..41,696.06 rows=87,917 width=8) (actual time=0.061..378.430 rows=105,856 loops=2)

  • Filter: (event_type = 'summary_judgment'::text)
  • Rows Removed by Filter: 1335340
9. 0.013 0.013 ↑ 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.013..0.013 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: 171603
10. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=111,975)

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

SubPlan (forGather)

12. 0.001 0.013 ↑ 1.0 1 106,095

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

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

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

  • Sort Key: ((iplc_case.id = event_1.case_id))
  • Sort Method: quicksort Memory: 25kB
14. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=106,095)

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