explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dFX5

Settings
# exclusive inclusive rows x rows loops node
1. 2.024 72.909 ↑ 1.7 1,128 1

Sort (cost=7,556.74..7,561.53 rows=1,916 width=233) (actual time=72.852..72.909 rows=1,128 loops=1)

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

HashAggregate (cost=7,418.75..7,452.28 rows=1,916 width=233) (actual time=69.973..70.885 rows=1,128 loops=1)

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

Hash Left Join (cost=7,320.42..7,399.59 rows=1,916 width=217) (actual time=67.845..68.972 rows=2,314 loops=1)

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

Hash Right Join (cost=7,278.85..7,344.42 rows=1,916 width=213) (actual time=67.629..68.438 rows=2,314 loops=1)

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

HashAggregate (cost=6,659.64..6,680.47 rows=2,083 width=12) (actual time=61.807..61.954 rows=1,266 loops=1)

  • Group Key: s.owner_id
6. 55.297 60.470 ↓ 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.338..60.470 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.173 5.173 ↓ 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.173..5.173 rows=63,815 loops=1)

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

Hash (cost=595.25..595.25 rows=1,916 width=205) (actual time=5.816..5.816 rows=2,314 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 538kB
9. 0.672 5.183 ↓ 1.2 2,314 1

Hash Right Join (cost=545.86..595.25 rows=1,916 width=205) (actual time=4.296..5.183 rows=2,314 loops=1)

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

HashAggregate (cost=119.64..135.33 rows=1,569 width=12) (actual time=1.835..2.055 rows=2,016 loops=1)

  • Group Key: s_1.owner_id
11. 1.536 1.536 ↑ 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.162..1.536 rows=2,016 loops=1)

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

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

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

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

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

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

15. 0.207 0.990 ↓ 1.2 2,314 1

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

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

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

  • Hash Cond: (devices_1.customer_id = customers.id)
17. 0.279 0.279 ↑ 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.279 rows=2,479 loops=1)