explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YfbZ

Settings
# exclusive inclusive rows x rows loops node
1. 7.146 98.654 ↓ 185.0 185 1

WindowAgg (cost=2,552.94..3,123.40 rows=1 width=203) (actual time=9.274..98.654 rows=185 loops=1)

2.          

CTE conversations

3. 1.638 5.468 ↓ 1.0 686 1

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

4. 1.023 1.086 ↑ 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.086 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.481 8.443 ↓ 185.0 185 1

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

8. 0.795 7.407 ↑ 1.1 185 1

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

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

CTE Scan on conversations (cost=0.00..13.56 rows=678 width=16) (actual time=0.117..6.612 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. 4.567 80.475 ↓ 0.0 0 185

Nested Loop Semi Join (cost=0.00..32.24 rows=1 width=0) (actual time=0.435..0.435 rows=0 loops=185)

  • Join Filter: (conversations_1.id = conversations_2.id)
  • Rows Removed by Join Filter: 25
14. 10.730 10.730 ↓ 7.0 7 185

CTE Scan on conversations conversations_1 (cost=0.00..16.95 rows=1 width=8) (actual time=0.006..0.058 rows=7 loops=185)

  • Filter: ((status_id = 7) AND (viewed_status_id = 1))
  • Rows Removed by Filter: 662
15. 65.178 65.178 ↓ 1.3 4 1,278

CTE Scan on conversations conversations_2 (cost=0.00..15.25 rows=3 width=8) (actual time=0.015..0.051 rows=4 loops=1,278)

  • Filter: (phone_id = conversations.phone_id)
  • Rows Removed by Filter: 680
Planning time : 0.756 ms
Execution time : 98.909 ms