explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bzhs

Settings
# exclusive inclusive rows x rows loops node
1. 0.100 35,621.414 ↓ 21.0 21 1

Sort (cost=414,134.48..414,134.49 rows=1 width=1,344) (actual time=35,621.399..35,621.414 rows=21 loops=1)

  • Sort Key: m.timecreated DESC
  • Sort Method: quicksort Memory: 44kB
2. 0.074 35,621.314 ↓ 21.0 21 1

Nested Loop Left Join (cost=2.68..414,134.47 rows=1 width=1,344) (actual time=5,062.894..35,621.314 rows=21 loops=1)

3. 0.082 35,620.736 ↓ 21.0 21 1

Nested Loop Left Join (cost=2.41..414,134.16 rows=1 width=1,344) (actual time=5,062.445..35,620.736 rows=21 loops=1)

4. 0.079 35,618.974 ↓ 21.0 21 1

Nested Loop (cost=2.13..414,133.86 rows=1 width=1,344) (actual time=5,062.436..35,618.974 rows=21 loops=1)

5. 0.133 35,618.748 ↓ 21.0 21 1

Nested Loop (cost=1.71..414,133.38 rows=1 width=1,336) (actual time=5,062.420..35,618.748 rows=21 loops=1)

  • Join Filter: ((m.conversationid = mc.id) AND ((m.useridfrom <> mcm.userid) OR (mc.type = 2)))
  • Rows Removed by Join Filter: 3
6. 173.772 35,618.279 ↓ 24.0 24 1

Nested Loop (cost=1.28..414,132.90 rows=1 width=1,344) (actual time=5,062.387..35,618.279 rows=24 loops=1)

7. 384.819 34,793.371 ↓ 162,784.0 162,784 1

Nested Loop (cost=0.85..414,132.34 rows=1 width=1,328) (actual time=0.202..34,793.371 rows=162,784 loops=1)

8. 211.919 33,755.740 ↓ 163,203.0 163,203 1

Nested Loop Left Join (cost=0.43..414,131.74 rows=1 width=1,320) (actual time=0.184..33,755.740 rows=163,203 loops=1)

  • Join Filter: (mua.messageid = m.id) Filter: (mua.id IS NULL)
9. 33,380.618 33,380.618 ↑ 1.0 163,203 1

Seq Scan on mdl_messages m (cost=0.00..411,671.92 rows=163,797 width=1,320) (actual time=0.127..33,380.618 rows=163,203 loops=1)

  • Filter: (smallmessage ~~* '%test%'::text)
  • Rows Removed by Filter: 1993193
10. 163.152 163.203 ↓ 0.0 0 163,203

Materialize (cost=0.43..2.86 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=163,203)

11. 0.051 0.051 ↓ 0.0 0 1

Index Scan using mdl_messuseracti_usemesact_uix on mdl_message_user_actions mua (cost=0.43..2.86 rows=1 width=16) (actual time=0.051..0.051 rows=0 loops=1)

  • Index Cond: ((userid = 85394) AND (action = 2))
12. 652.812 652.812 ↑ 1.0 1 163,203

Index Scan using mdl_user_id_pk on mdl_user u (cost=0.42..0.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=163,203)

  • Index Cond: (id = m.useridfrom)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 0
13. 651.136 651.136 ↓ 0.0 0 162,784

Index Scan using mdl_messconvmemb_con_ix on mdl_message_conversation_members mcm (cost=0.43..0.55 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=162,784)

  • Index Cond: (conversationid = m.conversationid)
  • Filter: ((m.useridfrom = 85394) OR (userid = 85394))
  • Rows Removed by Filter: 2
14. 0.336 0.336 ↑ 1.0 1 24

Index Scan using mdl_messconv_id_pk on mdl_message_conversations mc (cost=0.42..0.46 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=24)

  • Index Cond: (id = mcm.conversationid)
15. 0.147 0.147 ↑ 1.0 1 21

Index Scan using mdl_user_id_pk on mdl_user u2 (cost=0.42..0.47 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=21)

  • Index Cond: (id = mcm.userid)
  • Filter: (deleted = 0)
16. 1.680 1.680 ↓ 0.0 0 21

Index Scan using mdl_messuserbloc_blo_ix on mdl_message_users_blocked mub (cost=0.28..0.30 rows=1 width=16) (actual time=0.080..0.080 rows=0 loops=21)

  • Index Cond: (blockeduserid = u.id)
  • Filter: (userid = 85394)
  • Rows Removed by Filter: 0
17. 0.504 0.504 ↓ 0.0 0 21

Index Scan using mdl_messuserbloc_blo_ix on mdl_message_users_blocked mub2 (cost=0.28..0.30 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=21)

  • Index Cond: (blockeduserid = u2.id)
  • Filter: (userid = 85394)
Planning time : 17.073 ms
Execution time : 35,621.579 ms