explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3lUM : Optimization for: Optimization for: Optimization for: plan #9Iy7; plan #mMEL; plan #mXgnD

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.876 1,167.817 ↑ 1.1 1,504 1

Sort (cost=111,560.58..111,564.75 rows=1,668 width=187) (actual time=1,167.582..1,167.817 rows=1,504 loops=1)

  • Sort Key: (count(incidentrecord.incident_id)) DESC
  • Sort Method: quicksort Memory: 448kB
  • Buffers: shared hit=8,396 read=99,277
2. 13.163 1,164.941 ↑ 1.1 1,504 1

GroupAggregate (cost=111,412.93..111,471.31 rows=1,668 width=187) (actual time=1,151.41..1,164.941 rows=1,504 loops=1)

  • Group Key: ip.id, incidentrecord.source_ip
  • Buffers: shared hit=8,396 read=99,277
3. 1.810 1,151.778 ↓ 1.0 1,707 1

Sort (cost=111,412.93..111,417.1 rows=1,668 width=167) (actual time=1,151.372..1,151.778 rows=1,707 loops=1)

  • Sort Key: ip.id, incidentrecord.source_ip
  • Sort Method: quicksort Memory: 502kB
  • Buffers: shared hit=8,396 read=99,277
4. 21.685 1,149.968 ↓ 1.0 1,707 1

Gather (cost=1,001.54..111,323.66 rows=1,668 width=167) (actual time=217.066..1,149.968 rows=1,707 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=8,396 read=99,277
5. 0.648 1,128.283 ↑ 1.2 569 3 / 3

Hash Join (cost=1.54..110,156.86 rows=695 width=167) (actual time=245.284..1,128.283 rows=569 loops=3)

  • Buffers: shared hit=8,396 read=99,277
6. 5.778 1,127.590 ↑ 1.2 569 3 / 3

Nested Loop (cost=0.43..110,152.37 rows=695 width=151) (actual time=245.1..1,127.59 rows=569 loops=3)

  • Buffers: shared hit=8,341 read=99,277
7. 1,099.621 1,099.621 ↑ 1.2 569 3 / 3

Seq Scan on incidentrecord incidentrecord (cost=0..104,327.96 rows=695 width=43) (actual time=244.968..1,099.621 rows=569 loops=3)

  • Filter: ((incidentrecord.start_time > '2019-09-23 00:00:00'::timestamp without time zone) AND (incidentrecord.start_time < '2019-10-01 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=1,509 read=99,277
8. 22.191 22.191 ↑ 1.0 1 1,707 / 3

Index Scan using ip_ip on ip ip (cost=0.43..8.38 rows=1 width=108) (actual time=0.039..0.039 rows=1 loops=1,707)

  • Index Cond: (ip.ip = incidentrecord.source_ip)
  • Buffers: shared hit=6,832
9. 0.007 0.045 ↑ 1.0 5 3 / 3

Hash (cost=1.05..1.05 rows=5 width=48) (actual time=0.045..0.045 rows=5 loops=3)

  • Buffers: shared hit=3
10. 0.038 0.038 ↑ 1.0 5 3 / 3

Seq Scan on env env (cost=0..1.05 rows=5 width=48) (actual time=0.037..0.038 rows=5 loops=3)

  • Buffers: shared hit=3