explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ulaB

Settings
# exclusive inclusive rows x rows loops node
1. 3.940 827.835 ↓ 1.5 7,706 1

Nested Loop Left Join (cost=16,831.18..68,075.54 rows=5,278 width=8) (actual time=87.742..827.835 rows=7,706 loops=1)

  • Filter: (((CASE WHEN ((d.location)::text <> ''::text) THEN d.location ELSE parent.location END))::text <> ''::text)
2. 25.707 384.653 ↓ 1.5 7,706 1

Hash Join (cost=16,830.33..59,018.14 rows=5,304 width=4) (actual time=87.665..384.653 rows=7,706 loops=1)

  • Hash Cond: (ud_perm.device_id = devices.id)
3. 280.525 293.693 ↓ 1.1 33,030 1

Bitmap Heap Scan on permissions_device ud_perm (cost=868.06..42,324.94 rows=31,436 width=4) (actual time=17.384..293.693 rows=33,030 loops=1)

  • Recheck Cond: (user_id = 100)
  • Heap Blocks: exact=24974
4. 13.168 13.168 ↓ 1.1 35,055 1

Bitmap Index Scan on ix_permissions_user_id (cost=0.00..860.20 rows=31,436 width=0) (actual time=13.168..13.168 rows=35,055 loops=1)

  • Index Cond: (user_id = 100)
5. 2.527 65.253 ↑ 10.4 7,706 1

Hash (cost=14,644.45..14,644.45 rows=80,306 width=4) (actual time=65.253..65.253 rows=7,706 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1164kB
6. 62.726 62.726 ↑ 10.4 7,706 1

Index Scan using idx_devices_company_wh_status on devices (cost=0.42..14,644.45 rows=80,306 width=4) (actual time=0.023..62.726 rows=7,706 loops=1)

  • Index Cond: (company_id = 100)
  • Filter: (((category)::text = 'Meter'::text) AND ((status = 0) OR (status = '-1'::integer) OR (status = '-3'::integer) OR (status = '-4'::integer) OR (status <> ALL ('{0,-1,-3,-4}'::integer[]))))
  • Rows Removed by Filter: 4115
7. 7.706 439.242 ↑ 1.0 1 7,706

Nested Loop Left Join (cost=0.84..1.70 rows=1 width=36) (actual time=0.013..0.057 rows=1 loops=7,706)

8. 262.004 262.004 ↑ 1.0 1 7,706

Index Scan using idx_device_id on devices d (cost=0.42..1.13 rows=1 width=11) (actual time=0.007..0.034 rows=1 loops=7,706)

  • Index Cond: (id = devices.id)
9. 169.532 169.532 ↑ 1.0 1 7,706

Index Scan using idx_device_id on devices parent (cost=0.42..0.56 rows=1 width=7) (actual time=0.005..0.022 rows=1 loops=7,706)

  • Index Cond: (id = d.parent_device_id)
Planning time : 1.667 ms
Execution time : 829.071 ms