explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W4EOR

Settings
# exclusive inclusive rows x rows loops node
1. 7.817 36.895 ↓ 185.0 185 1

WindowAgg (cost=2,552.94..3,114.42 rows=1 width=203) (actual time=11.366..36.895 rows=185 loops=1)

2.          

CTE conversations

3. 1.842 7.550 ↓ 1.0 686 1

Nested Loop (cost=16.16..2,535.55 rows=678 width=16) (actual time=0.165..7.550 rows=686 loops=1)

4. 1.493 1.592 ↑ 1.0 686 1

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

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

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

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

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

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

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

8. 0.921 9.631 ↑ 1.1 185 1

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

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

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

10. 0.740 0.740 ↑ 1.0 1 185

Index Scan using queue_pkey on queue eq (cost=0.43..2.66 rows=1 width=136) (actual time=0.004..0.004 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.775 2.775 ↑ 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.014..0.015 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. 1.366 15.540 ↓ 0.0 0 185

Nested Loop (cost=15.70..23.27 rows=1 width=0) (actual time=0.084..0.084 rows=0 loops=185)

14. 1.480 11.470 ↓ 1.3 4 185

HashAggregate (cost=15.26..15.29 rows=3 width=8) (actual time=0.059..0.062 rows=4 loops=185)

  • Group Key: conversations_1.id
15. 9.990 9.990 ↓ 1.3 4 185

CTE Scan on conversations conversations_1 (cost=0.00..15.25 rows=3 width=8) (actual time=0.016..0.054 rows=4 loops=185)

  • Filter: (phone_id = conversations.phone_id)
  • Rows Removed by Filter: 682
16. 2.704 2.704 ↓ 0.0 0 676

Index Scan using queue_pkey on queue (cost=0.43..2.66 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=676)

  • Index Cond: (id = conversations_1.id)
  • Filter: ((status_id = 7) AND (viewed_status_id = 1))
  • Rows Removed by Filter: 1
Planning time : 1.464 ms
Execution time : 37.218 ms