explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vBJD

Settings
# exclusive inclusive rows x rows loops node
1. 5.959 26.744 ↓ 49.0 49 1

Nested Loop Semi Join (cost=612.39..637.85 rows=1 width=197) (actual time=13.117..26.744 rows=49 loops=1)

  • Join Filter: (devices.id = s_1.owner_id)
  • Rows Removed by Join Filter: 95206
2. 0.007 16.081 ↓ 49.0 49 1

Nested Loop Left Join (cost=610.86..612.23 rows=1 width=209) (actual time=11.040..16.081 rows=49 loops=1)

3. 0.327 16.025 ↓ 49.0 49 1

Nested Loop Semi Join (cost=610.59..611.89 rows=1 width=209) (actual time=11.037..16.025 rows=49 loops=1)

4. 1.024 13.366 ↓ 1,166.0 1,166 1

Nested Loop (cost=610.16..610.60 rows=1 width=205) (actual time=10.887..13.366 rows=1,166 loops=1)

5. 1.534 11.073 ↓ 1,269.0 1,269 1

HashAggregate (cost=609.88..609.89 rows=1 width=8) (actual time=10.880..11.073 rows=1,269 loops=1)

  • Group Key: s.owner_id
6. 0.618 9.539 ↓ 9,464.0 9,464 1

Nested Loop (cost=1.52..609.88 rows=1 width=8) (actual time=1.169..9.539 rows=9,464 loops=1)

7. 0.009 0.009 ↑ 1.0 1 1

Index Scan using index_keys_on_lower_key_text_pattern_ops on keys k (cost=0.42..8.44 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (lower((key)::text) = 'packages'::text)
8. 0.704 8.912 ↓ 242.7 9,464 1

Nested Loop (cost=1.10..601.05 rows=39 width=16) (actual time=1.160..8.912 rows=9,464 loops=1)

9. 4.092 4.092 ↓ 8.8 343 1

Index Scan using index_values_on_lower_value_text_pattern_ops on "values" v (cost=0.55..266.05 rows=39 width=8) (actual time=1.150..4.092 rows=343 loops=1)

  • Index Cond: ((lower((value)::text) ~>=~ 'microsoft'::text) AND (lower((value)::text) ~<~ 'microsofu'::text))
  • Filter: (lower((value)::text) ~~ 'microsoft%office%'::text)
  • Rows Removed by Filter: 2494
10. 4.116 4.116 ↓ 28.0 28 343

Index Only Scan using searchables_pkey on searchables s (cost=0.56..8.58 rows=1 width=24) (actual time=0.003..0.012 rows=28 loops=343)

  • Index Cond: ((key_id = k.id) AND (value_id = v.id))
  • Heap Fetches: 2629
11. 1.269 1.269 ↑ 1.0 1 1,269

Index Scan using devices_pkey on devices (cost=0.28..0.70 rows=1 width=197) (actual time=0.001..0.001 rows=1 loops=1,269)

  • Index Cond: (id = s.owner_id)
12. 0.000 2.332 ↓ 0.0 0 1,166

Nested Loop (cost=0.42..0.85 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,166)

13. 1.166 1.166 ↑ 1.0 1 1,166

Index Scan using devices_pkey on devices devices_1 (cost=0.28..0.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,166)

  • Index Cond: (id = devices.id)
14. 1.166 1.166 ↓ 0.0 0 1,166

Index Scan using customers_pkey on customers (cost=0.14..0.18 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,166)

  • Index Cond: (id = devices_1.customer_id)
  • Filter: (((shortname)::text ~~ '%taxat%'::text) AND (organization_id = 1))
  • Rows Removed by Filter: 1
15. 0.049 0.049 ↓ 0.0 0 49

Index Only Scan using index_contacts_on_device_id on contacts (cost=0.28..0.34 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=49)

  • Index Cond: (device_id = devices.id)
  • Heap Fetches: 0
16. 2.941 4.704 ↓ 1,944.0 1,944 49

Materialize (cost=1.52..25.61 rows=1 width=8) (actual time=0.001..0.096 rows=1,944 loops=49)

17. 0.128 1.763 ↓ 2,005.0 2,005 1

Nested Loop (cost=1.52..25.60 rows=1 width=8) (actual time=0.026..1.763 rows=2,005 loops=1)

18. 0.007 0.007 ↓ 2.0 2 1

Index Scan using index_values_on_lower_value_text_pattern_ops on "values" v_1 (cost=0.55..8.56 rows=1 width=8) (actual time=0.006..0.007 rows=2 loops=1)

  • Index Cond: (lower((value)::text) = 'windows'::text)
19. 0.134 1.628 ↓ 1,002.0 1,002 2

Nested Loop (cost=0.98..17.03 rows=1 width=16) (actual time=0.009..0.814 rows=1,002 loops=2)

20. 0.006 0.006 ↑ 1.0 1 2

Index Scan using index_keys_on_lower_key_text_pattern_ops on keys k_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (lower((key)::text) = 'os'::text)
21. 1.488 1.488 ↓ 1,002.0 1,002 2

Index Only Scan using searchables_pkey on searchables s_1 (cost=0.56..8.58 rows=1 width=24) (actual time=0.005..0.744 rows=1,002 loops=2)

  • Index Cond: ((key_id = k_1.id) AND (value_id = v_1.id))
  • Heap Fetches: 1382