explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SZSZ : netdisco #735

Settings
# exclusive inclusive rows x rows loops node
1. 208.935 565.038 ↓ 0.0 0 1

Hash Right Join (cost=99,286.18..103,869.07 rows=28,202 width=297) (actual time=565.038..565.038 rows=0 loops=1)

  • Hash Cond: (device_ips.ip = me.ip)
  • Filter: ((me.contact ~~* '%searchterm%'::text) OR (me.serial ~~* '%searchterm%'::text) OR (me.location ~~* '%searchterm%'::text) OR (me.name ~~* '%searchterm%'::text) OR (me.description ~~* '%searchterm%'::text) OR (hashed SubPlan 1) OR ((me.mac)::text ~~* '%searchterm%'::text) OR ((ports.mac)::text ~~* '%searchterm%'::text) OR (me.dns ~~* '%searchterm%'::text) OR (device_ips.dns ~~* '%searchterm%'::text) OR ((me.ip)::text ~~* '%searchterm%'::text) OR ((device_ips.alias)::text ~~* '%searchterm%'::text))
  • Rows Removed by Filter: 25,599
2. 5.595 5.595 ↑ 2.2 25,599 1

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

3. 2.068 216.078 ↑ 4.3 7,478 1

Hash (cost=56,760.84..56,760.84 rows=32,381 width=347) (actual time=216.078..216.078 rows=7,478 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,110kB
4. 2.274 214.010 ↑ 4.3 7,478 1

Hash Right Join (cost=6,492.57..56,760.84 rows=32,381 width=347) (actual time=211.902..214.010 rows=7,478 loops=1)

  • Hash Cond: (ports.ip = me.ip)
5. 2.290 192.064 ↓ 0.0 0 1

Gather (cost=1,000.00..51,268.08 rows=74 width=13) (actual time=192.002..192.064 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 189.774 189.774 ↓ 0.0 0 3 / 3

Parallel Seq Scan on device_port ports (cost=0.00..50,260.68 rows=31 width=13) (actual time=189.774..189.774 rows=0 loops=3)

  • Filter: ((mac)::text ~~* '%searchterm%'::text)
  • Rows Removed by Filter: 148,856
7. 4.188 19.672 ↑ 4.3 7,478 1

Hash (cost=5,087.81..5,087.81 rows=32,381 width=341) (actual time=19.672..19.672 rows=7,478 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 3,057kB
8. 15.484 15.484 ↑ 4.3 7,478 1

Seq Scan on device me (cost=0.00..5,087.81 rows=32,381 width=341) (actual time=0.009..15.484 rows=7,478 loops=1)

9.          

SubPlan (for Hash Right Join)

10. 2.732 134.430 ↓ 0.0 0 1

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

  • Workers Planned: 2
  • Workers Launched: 2
11. 0.001 131.698 ↓ 0.0 0 3 / 3

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

12. 131.697 131.697 ↓ 0.0 0 3 / 3

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

  • Filter: (serial = 'searchterm'::text)
  • Rows Removed by Filter: 214,600
13. 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 : 1.444 ms
Execution time : 565.254 ms