explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 76v

Settings
# exclusive inclusive rows x rows loops node
1. 478.009 9,754.159 ↑ 1.2 1,448,501 1

Finalize GroupAggregate (cost=489,825.32..761,181.53 rows=1,708,939 width=24) (actual time=8,505.417..9,754.159 rows=1,448,501 loops=1)

  • Group Key: e.case_id, e.event_type
2. 164.981 9,276.150 ↑ 1.3 1,448,501 1

Gather Merge (cost=489,825.32..729,745.68 rows=1,912,862 width=24) (actual time=8,505.407..9,276.150 rows=1,448,501 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
3. 407.533 9,111.169 ↓ 1.5 1,448,501 1

Partial GroupAggregate (cost=488,825.30..507,953.92 rows=956,431 width=24) (actual time=8,504.903..9,111.169 rows=1,448,501 loops=1)

  • Group Key: e.case_id, e.event_type
4. 1,691.811 8,703.636 ↓ 1.5 1,471,843 1

Sort (cost=488,825.30..491,216.37 rows=956,431 width=24) (actual time=8,504.892..8,703.636 rows=1,471,843 loops=1)

  • Sort Key: e.case_id, e.event_type
  • Sort Method: external merge Disk: 50552kB
5. 1,599.656 7,011.825 ↓ 1.5 1,471,843 1

Parallel Hash Join (cost=349,932.31..393,816.78 rows=956,431 width=24) (actual time=5,104.076..7,011.825 rows=1,471,843 loops=1)

  • Hash Cond: (e.case_id = c.id)
6. 329.714 329.714 ↓ 2.5 2,884,131 1

Parallel Seq Scan on event e (cost=0.00..36,823.43 rows=1,176,742 width=24) (actual time=0.017..329.714 rows=2,884,131 loops=1)

7. 548.463 5,082.455 ↓ 2.3 1,331,667 1

Parallel Hash (cost=342,697.99..342,697.99 rows=578,745 width=8) (actual time=5,082.455..5,082.455 rows=1,331,667 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 68480kB
8. 616.319 4,533.992 ↓ 2.3 1,331,667 1

Parallel Hash Anti Join (cost=168,099.57..342,697.99 rows=578,745 width=8) (actual time=1,223.022..4,533.992 rows=1,331,667 loops=1)

  • Hash Cond: (c.id = pct_1.case_id)
9. 1,556.110 3,814.590 ↓ 2.3 1,396,585 1

Parallel Hash Semi Join (cost=108,399.57..275,583.29 rows=596,032 width=8) (actual time=1,118.914..3,814.590 rows=1,396,585 loops=1)

  • Hash Cond: (c.id = pct.case_id)
10. 1,151.298 1,151.298 ↓ 2.4 2,581,436 1

Parallel Seq Scan on iplc_case c (cost=0.00..156,250.08 rows=1,071,508 width=4) (actual time=0.006..1,151.298 rows=2,581,436 loops=1)

11. 490.266 1,107.182 ↓ 2.4 1,445,247 1

Parallel Hash (cost=100,949.17..100,949.17 rows=596,032 width=4) (actual time=1,107.182..1,107.182 rows=1,445,247 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 72928kB
12. 519.705 616.916 ↓ 2.4 1,445,247 1

Parallel Bitmap Heap Scan on positve_case_tags pct (cost=31,715.38..100,949.17 rows=596,032 width=4) (actual time=106.887..616.916 rows=1,445,247 loops=1)

  • Recheck Cond: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432}'::integer[]))
  • Heap Blocks: exact=39970
13. 97.211 97.211 ↓ 1.0 1,446,574 1

Bitmap Index Scan on ix_positve_case_tags_tag_id (cost=0.00..31,357.76 rows=1,430,476 width=0) (actual time=97.211..97.211 rows=1,446,574 loops=1)

  • Index Cond: (tag_id = ANY ('{27,24,23,89,263,36,286,287,22,386,370,432}'::integer[]))
14. 22.184 103.083 ↓ 2.2 68,184 1

Parallel Hash (cost=59,311.55..59,311.55 rows=31,076 width=4) (actual time=103.083..103.083 rows=68,184 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3712kB
15. 72.480 80.899 ↓ 2.2 68,184 1

Parallel Bitmap Heap Scan on positve_case_tags pct_1 (cost=1,722.45..59,311.55 rows=31,076 width=4) (actual time=11.133..80.899 rows=68,184 loops=1)

  • Recheck Cond: (tag_id = 359)
  • Heap Blocks: exact=14281
16. 8.419 8.419 ↑ 1.0 71,548 1

Bitmap Index Scan on ix_positve_case_tags_tag_id (cost=0.00..1,703.81 rows=74,583 width=0) (actual time=8.419..8.419 rows=71,548 loops=1)

  • Index Cond: (tag_id = 359)
Planning time : 0.968 ms
Execution time : 9,847.286 ms