explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aXTd

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3,274.889 ↑ 1.0 5 1

Limit (cost=1,654.35..1,654.37 rows=5 width=1,300) (actual time=3,274.885..3,274.889 rows=5 loops=1)

2. 58.011 3,274.886 ↑ 2.0 5 1

Sort (cost=1,654.35..1,654.38 rows=10 width=1,300) (actual time=3,274.884..3,274.886 rows=5 loops=1)

  • Sort Key: (CASE WHEN v_messages_epoch.hi_priority THEN 0 ELSE 1 END), v_messages_epoch.msg_create_ts, v_messages_epoch.messageid
  • Sort Method: top-N heapsort Memory: 35kB
3. 18.542 3,216.875 ↓ 3,344.6 33,446 1

Subquery Scan on v_messages_epoch (cost=1,654.06..1,654.19 rows=10 width=1,300) (actual time=3,176.098..3,216.875 rows=33,446 loops=1)

4. 397.387 3,198.333 ↓ 3,344.6 33,446 1

Sort (cost=1,654.06..1,654.09 rows=10 width=1,308) (actual time=3,176.093..3,198.333 rows=33,446 loops=1)

  • Sort Key: (CASE WHEN messages.hi_priority THEN 0 ELSE 1 END), messages.message_create_ts
  • Sort Method: quicksort Memory: 67,962kB
5. 329.926 2,800.946 ↓ 3,344.6 33,446 1

Nested Loop Left Join (cost=1.39..1,653.90 rows=10 width=1,308) (actual time=0.531..2,800.946 rows=33,446 loops=1)

6. 17.817 664.936 ↓ 3,344.6 33,446 1

Nested Loop (cost=1.11..1,454.13 rows=10 width=1,280) (actual time=0.431..664.936 rows=33,446 loops=1)

7. 0.006 0.089 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.11..25.21 rows=1 width=55) (actual time=0.063..0.089 rows=1 loops=1)

  • Join Filter: (m1.gpssn = g.gpssn)
8. 0.013 0.013 ↑ 1.0 1 1

Index Scan using gprs_mobile_gpssn_idx on gprs_mobile m1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: (gpssn = 8,059,041)
9. 0.001 0.070 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.83..16.90 rows=1 width=51) (actual time=0.047..0.070 rows=1 loops=1)

10. 0.003 0.063 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.69..16.74 rows=1 width=35) (actual time=0.042..0.063 rows=1 loops=1)

11. 0.007 0.007 ↑ 1.0 1 1

Index Scan using gprs_mobile_gpssn_idx on gprs_mobile g (cost=0.28..8.30 rows=1 width=12) (actual time=0.005..0.007 rows=1 loops=1)

  • Index Cond: (gpssn = 8,059,041)
12. 0.053 0.053 ↑ 1.0 1 1

Index Scan using gprs_addr_pkey on gprs_addr ga (cost=0.41..8.43 rows=1 width=35) (actual time=0.035..0.053 rows=1 loops=1)

  • Index Cond: (g.id = gprs_mobileid)
13. 0.006 0.006 ↑ 1.0 1 1

Index Scan using gprs_addr_status_idx on gprs_addr_status stat (cost=0.14..0.15 rows=1 width=20) (actual time=0.004..0.006 rows=1 loops=1)

  • Index Cond: (ga.last_status = status)
14. 9.218 647.030 ↓ 109.7 33,446 1

Append (cost=0.00..1,425.87 rows=305 width=1,237) (actual time=0.367..647.030 rows=33,446 loops=1)

15. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on messages (cost=0.00..0.00 rows=1 width=101) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((delivery_status < 3) AND (m1.id = target_gprs_mobilid))
16. 637.808 637.808 ↓ 110.0 33,446 1

Index Scan using messages_fresh_target_gprs_mobilid_idx on messages_fresh (cost=0.42..1,425.87 rows=304 width=1,237) (actual time=0.363..637.808 rows=33,446 loops=1)

  • Index Cond: (target_gprs_mobilid = m1.id)
  • Filter: (delivery_status < 3)
  • Rows Removed by Filter: 204
17. 0.000 0.000 ↓ 0.0 0 33,446

Index Scan using gprs_mobile_pkey on gprs_mobile m2 (cost=0.28..0.30 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=33,446)

  • Index Cond: (messages.source_gprs_mobileid = id)
18.          

SubPlan (for Nested Loop Left Join)

19. 66.892 1,103.718 ↑ 1.0 1 33,446

Limit (cost=9.82..9.83 rows=1 width=16) (actual time=0.033..0.033 rows=1 loops=33,446)

20. 200.676 1,036.826 ↑ 54.0 1 33,446

Sort (cost=9.82..9.96 rows=54 width=16) (actual time=0.031..0.031 rows=1 loops=33,446)

  • Sort Key: msl.update_ts DESC
  • Sort Method: quicksort Memory: 25kB
21. 836.150 836.150 ↑ 54.0 1 33,446

Index Scan using message_status_log_idx on message_status_log msl (cost=0.57..9.55 rows=54 width=16) (actual time=0.025..0.025 rows=1 loops=33,446)

  • Index Cond: (messages_id = messages.id)
22. 33.446 702.366 ↑ 1.0 1 33,446

Limit (cost=9.82..9.83 rows=1 width=16) (actual time=0.021..0.021 rows=1 loops=33,446)

23. 200.676 668.920 ↑ 54.0 1 33,446

Sort (cost=9.82..9.96 rows=54 width=16) (actual time=0.020..0.020 rows=1 loops=33,446)

  • Sort Key: msl_1.update_ts DESC
  • Sort Method: quicksort Memory: 25kB
24. 468.244 468.244 ↑ 54.0 1 33,446

Index Scan using message_status_log_idx on message_status_log msl_1 (cost=0.57..9.55 rows=54 width=16) (actual time=0.014..0.014 rows=1 loops=33,446)

  • Index Cond: (messages_id = messages.id)