explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WxsC

Settings
# exclusive inclusive rows x rows loops node
1. 2.061 74.077 ↑ 1.7 1,128 1

Sort (cost=7,520.92..7,525.71 rows=1,916 width=233) (actual time=74.009..74.077 rows=1,128 loops=1)

  • Sort Key: (lower((devices.hostname)::text))
  • Sort Method: quicksort Memory: 354kB
2. 1.591 72.016 ↑ 1.7 1,128 1

HashAggregate (cost=7,392.51..7,416.46 rows=1,916 width=233) (actual time=71.360..72.016 rows=1,128 loops=1)

  • Group Key: devices.id, contacts.id
  • Filter: ((count(s.owner_id) > 0) AND (count(s_1.owner_id) > 0))
  • Rows Removed by Filter: 1186
3. 0.355 70.425 ↓ 1.2 2,314 1

Hash Left Join (cost=7,294.18..7,373.35 rows=1,916 width=209) (actual time=69.272..70.425 rows=2,314 loops=1)

  • Hash Cond: (devices.id = contacts.device_id)
4. 0.662 69.861 ↓ 1.2 2,314 1

Hash Right Join (cost=7,252.61..7,318.18 rows=1,916 width=205) (actual time=69.056..69.861 rows=2,314 loops=1)

  • Hash Cond: (s.owner_id = devices.id)
5. 1.354 63.336 ↑ 1.6 1,266 1

HashAggregate (cost=6,638.44..6,659.27 rows=2,083 width=4) (actual time=63.186..63.336 rows=1,266 loops=1)

  • Group Key: s.owner_id
6. 56.480 61.982 ↓ 1.1 9,455 1

Bitmap Heap Scan on searchable_table_101845 s (cost=2,012.25..6,617.24 rows=8,480 width=4) (actual time=5.671..61.982 rows=9,455 loops=1)

  • Filter: (((owner_type)::text = 'Device'::text) AND (lower(value) ~~ 'microsoft%office%'::text))
  • Rows Removed by Filter: 54360
  • Heap Blocks: exact=1411
7. 5.502 5.502 ↓ 1.1 63,815 1

Bitmap Index Scan on index_searchable_table_101845_lower_value (cost=0.00..2,010.13 rows=59,371 width=0) (actual time=5.502..5.502 rows=63,815 loops=1)

  • Index Cond: ((lower(value) ~>=~ 'microsoft'::text) AND (lower(value) ~<~ 'microsofu'::text))
8. 0.613 5.863 ↓ 1.2 2,314 1

Hash (cost=590.21..590.21 rows=1,916 width=201) (actual time=5.863..5.863 rows=2,314 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 525kB
9. 0.697 5.250 ↓ 1.2 2,314 1

Hash Right Join (cost=540.82..590.21 rows=1,916 width=201) (actual time=4.378..5.250 rows=2,314 loops=1)

  • Hash Cond: (s_1.owner_id = devices.id)
10. 0.414 1.947 ↓ 1.3 2,016 1

HashAggregate (cost=114.60..130.29 rows=1,569 width=4) (actual time=1.768..1.947 rows=2,016 loops=1)

  • Group Key: s_1.owner_id
11. 1.533 1.533 ↑ 1.0 2,016 1

Seq Scan on searchable_table_130602 s_1 (cost=0.00..109.56 rows=2,016 width=4) (actual time=0.168..1.533 rows=2,016 loops=1)

  • Filter: ((owner_type = 'Device'::text) AND (lower(value) = 'windows'::text))
  • Rows Removed by Filter: 2530
12. 0.591 2.606 ↓ 1.2 2,314 1

Hash (cost=402.27..402.27 rows=1,916 width=197) (actual time=2.606..2.606 rows=2,314 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 513kB
13. 0.721 2.015 ↓ 1.2 2,314 1

Hash Semi Join (cost=216.66..402.27 rows=1,916 width=197) (actual time=1.075..2.015 rows=2,314 loops=1)

  • Hash Cond: (devices.id = devices_1.id)
14. 0.226 0.226 ↑ 1.0 2,479 1

Seq Scan on devices (cost=0.00..157.79 rows=2,479 width=197) (actual time=0.004..0.226 rows=2,479 loops=1)

15. 0.208 1.068 ↓ 1.2 2,314 1

Hash (cost=192.71..192.71 rows=1,916 width=4) (actual time=1.068..1.068 rows=2,314 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 114kB
16. 0.551 0.860 ↓ 1.2 2,314 1

Hash Join (cost=6.46..192.71 rows=1,916 width=4) (actual time=0.027..0.860 rows=2,314 loops=1)

  • Hash Cond: (devices_1.customer_id = customers.id)
17. 0.286 0.286 ↑ 1.0 2,479 1

Seq Scan on devices devices_1 (cost=0.00..157.79 rows=2,479 width=8) (actual time=0.001..0.286 rows=2,479 loops=1)

18. 0.005 0.023 ↑ 1.0 51 1

Hash (cost=5.83..5.83 rows=51 width=4) (actual time=0.023..0.023 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.018 0.018 ↑ 1.0 51 1

Seq Scan on customers (cost=0.00..5.83 rows=51 width=4) (actual time=0.005..0.018 rows=51 loops=1)

  • Filter: (organization_id = 1)
  • Rows Removed by Filter: 15
20. 0.056 0.209 ↑ 1.8 520 1

Hash (cost=29.59..29.59 rows=959 width=8) (actual time=0.209..0.209 rows=520 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
21. 0.153 0.153 ↑ 1.0 959 1

Seq Scan on contacts (cost=0.00..29.59 rows=959 width=8) (actual time=0.007..0.153 rows=959 loops=1)