explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HUuW

Settings
# exclusive inclusive rows x rows loops node
1. 0.461 16,623.676 ↑ 245.3 132 1

Sort (cost=290,737.53..290,818.48 rows=32,381 width=547) (actual time=16,623.666..16,623.676 rows=132 loops=1)

  • Sort Key: me.dns, me.ip
  • Sort Method: quicksort Memory: 109kB
2. 4.938 16,623.215 ↑ 245.3 132 1

Group (cost=192,019.70..280,342.23 rows=32,381 width=547) (actual time=567.395..16,623.215 rows=132 loops=1)

  • Group Key: me.ip
3. 15,912.014 16,618.277 ↑ 8.2 82,858 1

Merge Left Join (cost=192,019.70..278,642.39 rows=679,935 width=547) (actual time=567.390..16,618.277 rows=82,858 loops=1)

  • Merge Cond: (me.ip = ports.ip)
  • Filter: ((me.contact ~~* '%bermo%'::text) OR (me.serial ~~* '%bermo%'::text) OR (me.location ~~* '%bermo%'::text) OR (me.name ~~* '%bermo%'::text) OR (me.description ~~* '%bermo%'::text) OR (hashed SubPlan 1) OR ((me.mac)::text ~~* '%bermo%'::text) OR ((ports.mac)::text ~~* '%bermo%'::text) OR (me.dns ~~* '%bermo%'::text) OR (device_ips.dns ~~* '%bermo%'::text) OR ((me.ip)::text ~~* '%bermo%'::text) OR ((device_ips.alias)::text ~~* '%bermo%'::text))
  • Rows Removed by Filter: 1,979,876
4. 12.499 53.490 ↑ 2.2 25,599 1

Gather Merge (cost=12,543.75..19,633.43 rows=56,362 width=580) (actual time=33.480..53.490 rows=25,599 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 5.828 40.991 ↑ 2.8 8,533 3 / 3

Merge Left Join (cost=11,543.73..12,127.84 rows=23,484 width=580) (actual time=30.622..40.991 rows=8,533 loops=3)

  • Merge Cond: (me.ip = device_ips.ip)
6. 3.824 14.447 ↑ 5.4 2,493 3 / 3

Sort (cost=5,824.46..5,858.19 rows=13,492 width=547) (actual time=13.228..14.447 rows=2,493 loops=3)

  • Sort Key: me.ip
  • Sort Method: quicksort Memory: 1,948kB
  • Worker 0: Sort Method: quicksort Memory: 1,507kB
  • Worker 1: Sort Method: quicksort Memory: 1,551kB
7. 10.623 10.623 ↑ 5.4 2,493 3 / 3

Parallel Seq Scan on device me (cost=0.00..4,898.92 rows=13,492 width=547) (actual time=0.018..10.623 rows=2,493 loops=3)

8. 12.835 20.716 ↑ 2.2 25,583 3 / 3

Sort (cost=5,719.27..5,860.17 rows=56,362 width=40) (actual time=17.386..20.716 rows=25,583 loops=3)

  • Sort Key: device_ips.ip
  • Sort Method: quicksort Memory: 2,248kB
  • Worker 0: Sort Method: quicksort Memory: 2,248kB
  • Worker 1: Sort Method: quicksort Memory: 2,248kB
9. 7.881 7.881 ↑ 2.2 25,599 3 / 3

Seq Scan on device_ip device_ips (cost=0.00..1,271.62 rows=56,362 width=40) (actual time=0.018..7.881 rows=25,599 loops=3)

10. 96.316 556.855 ↓ 2.8 2,062,721 1

Materialize (cost=137,355.38..141,067.53 rows=742,431 width=13) (actual time=412.214..556.855 rows=2,062,721 loops=1)

11. 207.818 460.539 ↑ 1.7 446,568 1

Sort (cost=137,355.38..139,211.45 rows=742,431 width=13) (actual time=412.211..460.539 rows=446,568 loops=1)

  • Sort Key: ports.ip
  • Sort Method: external merge Disk: 10,200kB
12. 252.721 252.721 ↑ 1.7 446,568 1

Seq Scan on device_port ports (cost=0.00..52,270.31 rows=742,431 width=13) (actual time=0.021..252.721 rows=446,568 loops=1)

13.          

SubPlan (for Merge Left Join)

14. 2.920 95.918 ↓ 0.0 0 1

Gather (cost=1,000.29..42,120.51 rows=28 width=7) (actual time=95.853..95.918 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 0.001 92.998 ↓ 0.0 0 3 / 3

Nested Loop (cost=0.29..41,117.71 rows=12 width=7) (actual time=92.997..92.998 rows=0 loops=3)

16. 92.997 92.997 ↓ 0.0 0 3 / 3

Parallel Seq Scan on device_module modules (cost=0.00..41,033.47 rows=12 width=7) (actual time=92.997..92.997 rows=0 loops=3)

  • Filter: (serial = 'bermo'::text)
  • Rows Removed by Filter: 214,600
17. 0.000 0.000 ↓ 0.0 0 / 3

Index Only Scan using device_pkey on device me_1 (cost=0.29..7.02 rows=1 width=7) (never executed)

  • Index Cond: (ip = modules.ip)
  • Heap Fetches: 0
Planning time : 2.927 ms
Execution time : 16,626.354 ms