explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.054 855.936 ↑ 1.1 1,504 1

Sort (cost=111,452.25..111,456.42 rows=1,666 width=187) (actual time=855.649..855.936 rows=1,504 loops=1)

  • Sort Key: (count(incidentrecord.incident_id)) DESC
  • Sort Method: quicksort Memory: 448kB
2. 12.849 852.882 ↑ 1.1 1,504 1

GroupAggregate (cost=111,304.8..111,363.11 rows=1,666 width=187) (actual time=839.539..852.882 rows=1,504 loops=1)

  • Group Key: ip.id, incidentrecord.source_ip
3. 2.925 840.033 ↓ 1.0 1,707 1

Sort (cost=111,304.8..111,308.96 rows=1,666 width=167) (actual time=839.496..840.033 rows=1,707 loops=1)

  • Sort Key: ip.id, incidentrecord.source_ip
  • Sort Method: quicksort Memory: 502kB
4. 17.995 837.108 ↓ 1.0 1,707 1

Gather (cost=1,001.54..111,215.65 rows=1,666 width=167) (actual time=87.482..837.108 rows=1,707 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 3.186 819.113 ↑ 1.2 569 3 / 3

Hash Join (cost=1.54..110,049.05 rows=694 width=167) (actual time=73.804..819.113 rows=569 loops=3)

6. 1.956 815.890 ↑ 1.2 569 3 / 3

Nested Loop (cost=0.43..110,044.57 rows=694 width=151) (actual time=71.095..815.89 rows=569 loops=3)

7. 793.450 793.450 ↑ 1.2 569 3 / 3

Seq Scan on incidentrecord incidentrecord (cost=0..104,228.59 rows=694 width=43) (actual time=71.039..793.45 rows=569 loops=3)

  • Filter: ((start_time > '2019-09-23 00:00:00'::timestamp without time zone) AND (start_time < '2019-10-01 00:00:00'::timestamp without time zone))
8. 20.484 20.484 ↑ 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.036..0.036 rows=1 loops=1,707)

  • Index Cond: (ip = incidentrecord.source_ip)
9. 0.007 0.037 ↑ 1.0 5 3 / 3

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

10. 0.030 0.030 ↑ 1.0 5 3 / 3

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