explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oXac

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 38,228.047 ↑ 1.0 20 1

Limit (cost=2,476,125.78..2,476,126.83 rows=20 width=286) (actual time=38,228.014..38,228.047 rows=20 loops=1)

2. 0.027 38,228.041 ↑ 668.5 20 1

Unique (cost=2,476,125.78..2,476,694.01 rows=13,370 width=258) (actual time=38,228.013..38,228.041 rows=20 loops=1)

3. 9.274 38,228.014 ↑ 668.5 20 1

Sort (cost=2,476,125.78..2,476,159.21 rows=13,370 width=258) (actual time=38,228.012..38,228.014 rows=20 loops=1)

  • Sort Key: al."timestamp" DESC, al.id DESC, al.action, al.user_email, al.entity_type, al.entity_ref_long, al.entity_ref_string, al.data, (CASE WHEN (al.entity_ref_long IS NOT NULL) THEN ((al.entity_ref_long)::text)::character varying ELSE al.entity_ref_string END), (CASE WHEN ((al.entity_type)::text = 'DOOR_SERIAL'::text) THEN max((d.serialno)::text) WHEN ((al.entity_type)::text = 'SMARTMODULE_IMEI'::text) THEN max((cm.imei)::text) WHEN ((al.entity_type)::text = 'DOORCONTROL_SERIAL'::text) THEN max((dc.serialno)::text) WHEN ((al.entity_type)::text = 'USER_EMAIL'::text) THEN max((du.email)::text) WHEN ((al.entity_type)::text = 'HOLDER_ID'::text) THEN max((h.name)::text) ELSE '-'::text END), (max(d.id)), (max(du.id)), (max(dc.id)), (max(h.id)), (max((cm.imei)::text)), (array_agg(hh.parent_id))
  • Sort Method: quicksort Memory: 4031kB
4. 61.197 38,218.740 ↑ 1.0 13,277 1

Aggregate (cost=2,454,503.82..2,475,209.49 rows=13,370 width=258) (actual time=38,141.431..38,218.74 rows=13,277 loops=1)

5. 161.585 38,157.543 ↑ 10.7 58,507 1

Sort (cost=2,454,503.82..2,456,065.71 rows=624,756 width=258) (actual time=38,141.397..38,157.543 rows=58,507 loops=1)

  • Sort Key: al.id DESC
  • Sort Method: external sort Disk: 8552kB
6. 153.902 37,995.958 ↑ 10.7 58,507 1

Nested Loop (cost=438.67..2,240,606.89 rows=624,756 width=258) (actual time=21.197..37,995.958 rows=58,507 loops=1)

7. 14.729 37,664.226 ↑ 2.9 35,566 1

Hash Join (cost=436.28..1,567,408.28 rows=102,664 width=266) (actual time=21.177..37,664.226 rows=35,566 loops=1)

8. 23.222 37,649.424 ↑ 2.0 35,418 1

Hash Join (cost=432..1,564,584.25 rows=71,724 width=258) (actual time=21.095..37,649.424 rows=35,418 loops=1)

9. 29,091.735 37,610.401 ↑ 2.0 13,277 1

Nested Loop (cost=1.12..1,563,143.03 rows=26,054 width=250) (actual time=5.171..37,610.401 rows=13,277 loops=1)

10. 4.535 247.095 ↑ 1.0 13,277 1

Nested Loop (cost=1.12..14,275.58 rows=13,370 width=222) (actual time=0.04..247.095 rows=13,277 loops=1)

11. 11.467 229.283 ↑ 1.0 13,277 1

Nested Loop (cost=0.85..10,117.73 rows=13,370 width=190) (actual time=0.033..229.283 rows=13,277 loops=1)

12. 8.713 177.985 ↑ 1.0 13,277 1

Nested Loop (cost=0.71..7,787.41 rows=13,370 width=158) (actual time=0.028..177.985 rows=13,277 loops=1)

13. 21.022 129.441 ↑ 1.0 13,277 1

Nested Loop (cost=0.57..5,502.51 rows=13,370 width=142) (actual time=0.02..129.441 rows=13,277 loops=1)

14. 15.480 15.480 ↑ 1.0 13,277 1

Index Scan using access_log_pkey on access_log al (cost=0.29..1,024.38 rows=13,370 width=126) (actual time=0.012..15.48 rows=13,277 loops=1)

15. 92.939 92.939 ↓ 0.0 0 13,277

Index Scan using doorcontrol_serialno_key on doorcontrol dc (cost=0.28..0.32 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=13,277)

  • Index Cond: ((serialno)::text = (al.entity_ref_string)::text)
16. 39.831 39.831 ↓ 0.0 0 13,277

Index Only Scan using commodule_pkey on commodule cm (cost=0.14..0.16 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=13,277)

  • Index Cond: (imei = (al.entity_ref_string)::text)
17. 39.831 39.831 ↓ 0.0 0 13,277

Index Scan using doco_user_email_key on doco_user du (cost=0.14..0.16 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=13,277)

  • Index Cond: ((email)::text = (al.entity_ref_string)::text)
18. 13.277 13.277 ↓ 0.0 0 13,277

Index Scan using holder_pkey on holder h (cost=0.28..0.3 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=13,277)

  • Index Cond: (id = al.entity_ref_long)
19. 8,269.943 8,271.571 ↑ 1.0 5,148 13,277

Materialize (cost=0..233.22 rows=5,148 width=52) (actual time=0..0.623 rows=5,148 loops=13,277)

20. 1.628 1.628 ↑ 1.0 5,148 1

Seq Scan on door d (cost=0..207.48 rows=5,148 width=52) (actual time=0.005..1.628 rows=5,148 loops=1)

21. 13.182 15.801 ↓ 1.0 14,184 1

Hash (cost=253.72..253.72 rows=14,172 width=16) (actual time=15.801..15.801 rows=14,184 loops=1)

22. 2.619 2.619 ↓ 1.0 14,184 1

Seq Scan on door_holder doorholder (cost=0..253.72 rows=14,172 width=16) (actual time=0.007..2.619 rows=14,184 loops=1)

23. 0.044 0.073 ↓ 1.2 171 1

Hash (cost=2.46..2.46 rows=146 width=16) (actual time=0.073..0.073 rows=171 loops=1)

24. 0.029 0.029 ↓ 1.2 171 1

Seq Scan on holder_users holderusers (cost=0..2.46 rows=146 width=16) (actual time=0.006..0.029 rows=171 loops=1)

25. 35.566 177.830 ↑ 3.0 2 35,566

Bitmap Heap Scan on holderhierarchy hh (cost=2.39..6.5 rows=6 width=16) (actual time=0.005..0.005 rows=2 loops=35,566)

26. 35.566 142.264 ↓ 0.0 0 35,566

BitmapOr (cost=2.39..2.39 rows=6 width=0) (actual time=0.004..0.004 rows=0 loops=35,566)

27. 71.132 71.132 ↑ 1.0 2 35,566

Bitmap Index Scan on custom_index_holderhierarchy_child_id (cost=0..0.29 rows=2 width=0) (actual time=0.002..0.002 rows=2 loops=35,566)

  • Index Cond: (child_id = doorholder.holders_id)
28. 35.566 35.566 ↓ 0.0 0 35,566

Bitmap Index Scan on custom_index_holderhierarchy_child_id (cost=0..0.51 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=35,566)

  • Index Cond: (child_id = holderusers.holder_id)
29. 0.000 0.000 ↓ 0.0 0 35,566

Bitmap Index Scan on custom_index_holderhierarchy_child_id (cost=0..0.33 rows=2 width=0) (actual time=0..0 rows=0 loops=35,566)

  • Index Cond: (child_id = h.id)