explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1o3n

Settings
# exclusive inclusive rows x rows loops node
1. 0.086 167.616 ↓ 5.0 5 1

Nested Loop Anti Join (cost=11.43..24.07 rows=1 width=197) (actual time=33.412..167.616 rows=5 loops=1)

2. 0.123 165.274 ↓ 48.0 48 1

Nested Loop Left Join (cost=10.03..18.18 rows=1 width=197) (actual time=10.405..165.274 rows=48 loops=1)

3. 0.121 164.959 ↓ 48.0 48 1

Nested Loop Semi Join (cost=9.76..17.88 rows=1 width=197) (actual time=10.394..164.959 rows=48 loops=1)

4. 2.705 141.353 ↓ 55.0 55 1

Nested Loop (cost=8.36..11.99 rows=1 width=205) (actual time=9.778..141.353 rows=55 loops=1)

5. 3.285 10.520 ↓ 2,002.0 2,002 1

HashAggregate (cost=8.08..8.09 rows=1 width=8) (actual time=9.213..10.520 rows=2,002 loops=1)

  • Group Key: s_1.owner_id
6. 0.887 7.235 ↓ 2,002.0 2,002 1

Nested Loop (cost=1.40..8.08 rows=1 width=8) (actual time=0.132..7.235 rows=2,002 loops=1)

7. 0.042 0.042 ↓ 3.0 3 1

Index Scan using values_lower_value_index on "values" v_1 (cost=0.42..2.64 rows=1 width=8) (actual time=0.036..0.042 rows=3 loops=1)

  • Index Cond: (lower((value)::text) = 'windows'::text)
8. 1.212 6.306 ↓ 667.0 667 3

Nested Loop (cost=0.98..5.43 rows=1 width=16) (actual time=0.042..2.102 rows=667 loops=3)

9. 0.063 0.063 ↑ 1.0 1 3

Index Scan using keys_key_index on keys k_1 (cost=0.42..2.64 rows=1 width=8) (actual time=0.020..0.021 rows=1 loops=3)

  • Index Cond: (lower((key)::text) = 'os'::text)
10. 5.031 5.031 ↓ 667.0 667 3

Index Scan using values_keys on searchables s_1 (cost=0.56..2.78 rows=1 width=24) (actual time=0.020..1.677 rows=667 loops=3)

  • Index Cond: ((value_id = v_1.id) AND (key_id = k_1.id))
  • Filter: ((owner_type)::text = 'Device'::text)
11. 20.886 128.128 ↓ 0.0 0 2,002

Index Scan using devices_pkey on devices (cost=0.28..3.90 rows=1 width=197) (actual time=0.064..0.064 rows=0 loops=2,002)

  • Index Cond: (id = s_1.owner_id)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 1
12.          

SubPlan (forIndex Scan)

13. 3.698 107.242 ↓ 0.0 0 1,849

Nested Loop (cost=0.28..3.56 rows=1 width=0) (actual time=0.058..0.058 rows=0 loops=1,849)

14. 7.396 7.396 ↑ 1.0 1 1,849

Index Only Scan using devices_pkey on devices d (cost=0.28..1.40 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=1,849)

  • Index Cond: (id = devices.id)
  • Heap Fetches: 0
15. 96.148 96.148 ↓ 0.0 0 1,849

Seq Scan on customers (cost=0.00..2.16 rows=1 width=0) (actual time=0.052..0.052 rows=0 loops=1,849)

  • Filter: (((shortname)::text ~~ '%taxat%'::text) AND (id = devices.customer_id) AND (organization_id = 1))
  • Rows Removed by Filter: 64
16. 1.970 23.485 ↑ 1.0 1 55

Nested Loop (cost=1.40..5.88 rows=1 width=8) (actual time=0.427..0.427 rows=1 loops=55)

17. 0.770 5.885 ↓ 28.0 28 55

Nested Loop (cost=0.98..5.43 rows=1 width=16) (actual time=0.052..0.107 rows=28 loops=55)

18. 1.650 1.650 ↑ 1.0 1 55

Index Scan using keys_key_index on keys k (cost=0.42..2.64 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=55)

  • Index Cond: (lower((key)::text) = 'packages'::text)
19. 3.465 3.465 ↓ 28.0 28 55

Index Scan using keys_values on searchables s (cost=0.56..2.78 rows=1 width=24) (actual time=0.020..0.063 rows=28 loops=55)

  • Index Cond: ((owner_id = devices.id) AND (key_id = k.id))
  • Filter: ((owner_type)::text = 'Device'::text)
20. 15.630 15.630 ↓ 0.0 0 1,563

Index Scan using values_pkey on "values" v (cost=0.42..0.44 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1,563)

  • Index Cond: (id = s.value_id)
  • Filter: (lower((value)::text) ~~ 'microsoft%office%'::text)
  • Rows Removed by Filter: 1
21. 0.192 0.192 ↓ 0.0 0 48

Index Only Scan using index_contacts_on_device_id on contacts (cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=48)

  • Index Cond: (device_id = devices.id)
  • Heap Fetches: 0
22. 0.144 2.256 ↑ 1.0 1 48

Nested Loop (cost=1.40..5.87 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=48)

23. 0.096 1.680 ↑ 1.0 1 48

Nested Loop (cost=0.98..5.43 rows=1 width=16) (actual time=0.035..0.035 rows=1 loops=48)

24. 1.008 1.008 ↑ 1.0 1 48

Index Scan using keys_key_index on keys k_2 (cost=0.42..2.64 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=48)

  • Index Cond: (lower((key)::text) = 'ipaddress'::text)
25. 0.576 0.576 ↑ 1.0 1 48

Index Scan using keys_values on searchables s_2 (cost=0.56..2.78 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=48)

  • Index Cond: ((owner_id = devices.id) AND (key_id = k_2.id))
  • Filter: ((owner_type)::text = 'Device'::text)
26. 0.432 0.432 ↑ 1.0 1 48

Index Scan using values_pkey on "values" v_2 (cost=0.42..0.44 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=48)

  • Index Cond: (id = s_2.value_id)
  • Filter: (lower((value)::text) ~~ '192.168.0%'::text)
  • Rows Removed by Filter: 0