explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qiFI : netdisco 735

Settings
# exclusive inclusive rows x rows loops node
1. 208.286 481.600 ↓ 0.0 0 1

Hash Right Join (cost=99,286.18..103,869.07 rows=28,202 width=297) (actual time=481.600..481.600 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. 3.972 3.972 ↑ 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.013..3.972 rows=25,599 loops=1)

3. 2.137 200.083 ↑ 4.3 7,478 1

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

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

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

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

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

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

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

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

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

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

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

9.          

SubPlan (for Hash Right Join)

10. 2.616 69.259 ↓ 0.0 0 1

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

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

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

12. 66.642 66.642 ↓ 0.0 0 3 / 3

Parallel Seq Scan on device_module modules (cost=0.00..41,033.47 rows=12 width=7) (actual time=66.642..66.642 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.670 ms
Execution time : 481.766 ms