explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wys7

Settings
# exclusive inclusive rows x rows loops node
1. 2.077 76.974 ↑ 1.7 1,128 1

Sort (cost=3,874.00..3,878.79 rows=1,916 width=233) (actual time=76.910..76.974 rows=1,128 loops=1)

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

HashAggregate (cost=3,736.01..3,769.54 rows=1,916 width=233) (actual time=73.991..74.897 rows=1,128 loops=1)

  • Group Key: devices.id, contacts.id
  • Filter: ((sum(attr9d124757b77db06889c1ff27b4d99375.num) > '0'::numeric) AND (sum(attr316d1ef517f382f0a1e34f633f69dbf6.num) > '0'::numeric))
  • Rows Removed by Filter: 1186
3. 0.383 72.894 ↓ 1.2 2,314 1

Hash Left Join (cost=3,502.78..3,716.85 rows=1,916 width=217) (actual time=70.446..72.894 rows=2,314 loops=1)

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

Hash Left Join (cost=3,461.21..3,661.67 rows=1,916 width=213) (actual time=70.234..72.302 rows=2,314 loops=1)

  • Hash Cond: (devices.id = attr316d1ef517f382f0a1e34f633f69dbf6.id)
5. 0.406 70.584 ↓ 1.2 2,314 1

Hash Left Join (cost=3,425.87..3,618.97 rows=1,916 width=205) (actual time=68.993..70.584 rows=2,314 loops=1)

  • Hash Cond: (devices.id = attr9d124757b77db06889c1ff27b4d99375.id)
6. 0.850 2.996 ↓ 1.2 2,314 1

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

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

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

8. 0.411 1.796 ↓ 1.2 2,314 1

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

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 114kB
9. 0.840 1.385 ↓ 1.2 2,314 1

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

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

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

11. 0.009 0.036 ↑ 1.0 51 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.027 0.027 ↑ 1.0 51 1

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

  • Filter: (organization_id = 1)
  • Rows Removed by Filter: 15
13. 0.136 67.182 ↓ 32.5 1,266 1

Hash (cost=3,208.73..3,208.73 rows=39 width=12) (actual time=67.182..67.182 rows=1,266 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 71kB
14. 0.088 67.046 ↓ 32.5 1,266 1

Subquery Scan on attr9d124757b77db06889c1ff27b4d99375 (cost=3,207.65..3,208.73 rows=39 width=12) (actual time=65.569..67.046 rows=1,266 loops=1)

15. 0.899 66.958 ↓ 32.5 1,266 1

GroupAggregate (cost=3,207.65..3,208.34 rows=39 width=12) (actual time=65.567..66.958 rows=1,266 loops=1)

  • Group Key: s.owner_id
16. 1.719 66.059 ↓ 242.4 9,455 1

Sort (cost=3,207.65..3,207.75 rows=39 width=4) (actual time=65.561..66.059 rows=9,455 loops=1)

  • Sort Key: s.owner_id
  • Sort Method: quicksort Memory: 828kB
17. 55.865 64.340 ↓ 242.4 9,455 1

Bitmap Heap Scan on searchable_table_101845 s (cost=67.77..3,206.62 rows=39 width=4) (actual time=8.650..64.340 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
18. 8.475 8.475 ↓ 33.0 63,815 1

Bitmap Index Scan on index_searchable_table_101845_lower_value (cost=0.00..67.76 rows=1,934 width=0) (actual time=8.475..8.475 rows=63,815 loops=1)

  • Index Cond: ((lower(value) ~>=~ 'microsoft'::text) AND (lower(value) ~<~ 'microsofu'::text))
19. 0.176 1.232 ↓ 87.7 2,016 1

Hash (cost=35.05..35.05 rows=23 width=12) (actual time=1.232..1.232 rows=2,016 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 103kB
20. 0.129 1.056 ↓ 87.7 2,016 1

Subquery Scan on attr316d1ef517f382f0a1e34f633f69dbf6 (cost=34.59..35.05 rows=23 width=12) (actual time=0.746..1.056 rows=2,016 loops=1)

21. 0.548 0.927 ↓ 87.7 2,016 1

HashAggregate (cost=34.59..34.82 rows=23 width=12) (actual time=0.746..0.927 rows=2,016 loops=1)

  • Group Key: s_1.owner_id
22. 0.255 0.379 ↓ 87.7 2,016 1

Bitmap Heap Scan on searchable_table_130602 s_1 (cost=4.46..34.47 rows=23 width=4) (actual time=0.133..0.379 rows=2,016 loops=1)

  • Recheck Cond: (lower(value) = 'windows'::text)
  • Filter: (owner_type = 'Device'::text)
  • Heap Blocks: exact=14
23. 0.124 0.124 ↓ 87.7 2,016 1

Bitmap Index Scan on index_searchable_table_130602_value (cost=0.00..4.46 rows=23 width=0) (actual time=0.124..0.124 rows=2,016 loops=1)

  • Index Cond: (lower(value) = 'windows'::text)
24. 0.063 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
25. 0.146 0.146 ↑ 1.0 959 1

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