explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1TV6

Settings
# exclusive inclusive rows x rows loops node
1. 6.716 17.978 ↓ 185.0 185 1

WindowAgg (cost=2,552.94..3,109.80 rows=1 width=203) (actual time=9.065..17.978 rows=185 loops=1)

2.          

CTE conversations

3. 1.774 5.593 ↓ 1.0 686 1

Nested Loop (cost=16.16..2,535.55 rows=678 width=22) (actual time=0.113..5.593 rows=686 loops=1)

4. 1.012 1.075 ↑ 1.0 686 1

Bitmap Heap Scan on sms_conversations sc (cost=15.72..688.36 rows=697 width=16) (actual time=0.098..1.075 rows=686 loops=1)

  • Recheck Cond: (company_id = 1,364)
  • Heap Blocks: exact=302
5. 0.063 0.063 ↑ 1.0 686 1

Bitmap Index Scan on idx_sms_conversations_complex (cost=0.00..15.55 rows=697 width=0) (actual time=0.062..0.063 rows=686 loops=1)

  • Index Cond: (company_id = 1,364)
6. 2.744 2.744 ↑ 1.0 1 686

Index Scan using queue_pkey on queue eq_1 (cost=0.43..2.65 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=686)

  • Index Cond: (id = sc.queue_id)
  • Filter: (status_id = ANY ('{1,2,3,4,7,8,11}'::integer[]))
7. 0.461 8.610 ↓ 185.0 185 1

Nested Loop (cost=17.38..552.99 rows=1 width=144) (actual time=7.459..8.610 rows=185 loops=1)

8. 0.838 7.594 ↑ 1.1 185 1

HashAggregate (cost=16.95..18.95 rows=200 width=16) (actual time=7.448..7.594 rows=185 loops=1)

  • Group Key: conversations.phone_id
9. 6.756 6.756 ↓ 1.0 686 1

CTE Scan on conversations (cost=0.00..13.56 rows=678 width=16) (actual time=0.117..6.756 rows=686 loops=1)

10. 0.555 0.555 ↑ 1.0 1 185

Index Scan using queue_pkey on queue eq (cost=0.43..2.66 rows=1 width=136) (actual time=0.003..0.003 rows=1 loops=185)

  • Index Cond: (id = (max(conversations.id)))
  • Filter: ((COALESCE(group_id, 0) <> ALL ('{3,20}'::integer[])) AND (company_id = 1,364) AND (type_id = 2))
11.          

SubPlan (for WindowAgg)

12. 2.590 2.590 ↑ 1.0 1 185

Index Scan using idx_registered_numbers_company_id on registered_numbers (cost=0.27..2.53 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=185)

  • Index Cond: (company_id = 1,364)
  • Filter: ((phone)::text ~* (pmt_get_client_phone_number((eq.phone_account_number)::text, 0))::text)
  • Rows Removed by Filter: 0
13. 0.000 0.000 ↓ 0.0 0

CTE Scan on conversations conversations_1 (cost=0.00..18.65 rows=1 width=0) (never executed)

  • Filter: ((phone_id = conversations.phone_id) AND (status_id = 7) AND (viewed_status_id = 1))
14. 0.062 0.062 ↓ 7.0 7 1

CTE Scan on conversations conversations_2 (cost=0.00..16.95 rows=1 width=8) (actual time=0.007..0.062 rows=7 loops=1)

  • Filter: ((status_id = 7) AND (viewed_status_id = 1))
  • Rows Removed by Filter: 679
Planning time : 1.086 ms
Execution time : 18.228 ms