explain.depesz.com

PostgreSQL's explain analyze made readable

Result: L16l

Settings
# exclusive inclusive rows x rows loops node
1. 0.257 626.030 ↓ 23.5 305 1

Sort (cost=111,738.20..111,738.24 rows=13 width=8) (actual time=625.999..626.030 rows=305 loops=1)

  • Sort Key: (((min(event.date)) - (min(event_1.date))))
  • Sort Method: quicksort Memory: 39kB
2. 0.130 625.773 ↓ 23.5 305 1

Nested Loop Anti Join (cost=107,051.18..111,737.96 rows=13 width=8) (actual time=535.832..625.773 rows=305 loops=1)

3. 0.282 625.031 ↓ 23.5 306 1

Nested Loop Semi Join (cost=107,050.75..111,730.96 rows=13 width=12) (actual time=535.824..625.031 rows=306 loops=1)

4. 20.666 618.308 ↓ 14.1 339 1

Hash Join (cost=107,050.31..111,713.93 rows=24 width=20) (actual time=535.781..618.308 rows=339 loops=1)

  • Hash Cond: (event.case_id = iplc_case.id)
  • Join Filter: (((min(event.date)) - (min(event_1.date))) > 0)
  • Rows Removed by Join Filter: 408
5. 182.860 422.353 ↓ 1.1 211,816 1

Finalize HashAggregate (cost=59,948.01..61,911.18 rows=196,317 width=8) (actual time=360.254..422.353 rows=211,816 loops=1)

  • Group Key: event.case_id
6. 37.302 239.493 ↓ 1.3 211,816 1

Gather (cost=41,495.73..59,109.27 rows=167,748 width=8) (actual time=185.207..239.493 rows=211,816 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 56.560 202.191 ↑ 1.2 70,605 3

Partial HashAggregate (cost=40,495.73..41,334.47 rows=83,874 width=8) (actual time=179.532..202.191 rows=70,605 loops=3)

  • Group Key: event.case_id
8. 145.631 145.631 ↑ 1.2 70,605 3

Parallel Seq Scan on event (cost=0.00..40,076.36 rows=83,874 width=8) (actual time=0.021..145.631 rows=70,605 loops=3)

  • Filter: (event_type = 'summary_judgment'::text)
  • Rows Removed by Filter: 890775
9. 0.595 175.289 ↓ 1.6 1,551 1

Hash (cost=47,090.28..47,090.28 rows=961 width=12) (actual time=175.288..175.289 rows=1,551 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 83kB
10. 0.000 174.694 ↓ 1.6 1,551 1

Nested Loop (cost=41,094.11..47,090.28 rows=961 width=12) (actual time=162.846..174.694 rows=1,551 loops=1)

11. 0.429 163.869 ↓ 1.6 1,551 1

Finalize GroupAggregate (cost=41,093.68..41,206.63 rows=961 width=8) (actual time=162.758..163.869 rows=1,551 loops=1)

  • Group Key: event_1.case_id
12. 0.000 163.440 ↓ 1.9 1,551 1

Gather Merge (cost=41,093.68..41,193.02 rows=800 width=8) (actual time=162.751..163.440 rows=1,551 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 0.627 477.822 ↓ 1.3 517 3

Partial GroupAggregate (cost=40,093.65..40,100.65 rows=400 width=8) (actual time=158.987..159.274 rows=517 loops=3)

  • Group Key: event_1.case_id
14. 1.485 477.195 ↓ 1.3 517 3

Sort (cost=40,093.65..40,094.65 rows=400 width=8) (actual time=158.978..159.065 rows=517 loops=3)

  • Sort Key: event_1.case_id
  • Sort Method: quicksort Memory: 56kB
  • Worker 0: Sort Method: quicksort Memory: 45kB
  • Worker 1: Sort Method: quicksort Memory: 45kB
15. 475.710 475.710 ↓ 1.3 517 3

Parallel Seq Scan on event event_1 (cost=0.00..40,076.36 rows=400 width=8) (actual time=0.119..158.570 rows=517 loops=3)

  • Filter: (event_type = 'class_certification'::text)
  • Rows Removed by Filter: 960863
16. 10.857 10.857 ↑ 1.0 1 1,551

Index Only Scan using iplc_case_id_nos_code_idx on iplc_case (cost=0.43..6.11 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,551)

  • Index Cond: (id = event_1.case_id)
  • Heap Fetches: 793
17. 6.441 6.441 ↑ 1.0 1 339

Index Only Scan using positve_case_tags_pkey on positve_case_tags (cost=0.43..0.70 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=339)

  • 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: 5
  • Heap Fetches: 683
18. 0.612 0.612 ↓ 0.0 0 306

Index Only Scan using positve_case_tags_pkey on positve_case_tags positve_case_tags_1 (cost=0.43..0.53 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=306)

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