explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lhHb

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 801.982 ↑ 1.0 1 1

Limit (cost=13,206.59..13,206.59 rows=1 width=91) (actual time=801.981..801.982 rows=1 loops=1)

2. 0.011 801.980 ↑ 1.0 1 1

Sort (cost=13,206.59..13,206.59 rows=1 width=91) (actual time=801.980..801.980 rows=1 loops=1)

  • Sort Key: (((c.fields -> 'sla'::text) ->> 'dueAt'::text))
  • Sort Method: quicksort Memory: 25kB
3. 0.021 801.969 ↑ 1.0 1 1

Nested Loop Left Join (cost=9,510.02..13,206.58 rows=1 width=91) (actual time=801.967..801.969 rows=1 loops=1)

  • Filter: (active_realtime.id IS NULL)
4. 556.149 801.925 ↑ 434.0 1 1

Bitmap Heap Scan on conversations c (cost=9,509.52..9,521.64 rows=434 width=618) (actual time=801.924..801.925 rows=1 loops=1)

  • Recheck Cond: (((fields ->> 'status'::text) <> 'CLOSED'::text) AND (((fields -> 'assignee'::text) ->> 'routingGroupId'::text) = ANY ('{j0NYS6ogTL2yRaQXLm3yyg,SE2DikRYRNyKZN2qL5LwHA,ujlw9fbaRauxhe2n1Wyr6Q}'::text[])) AND ((fields ->> 'status'::text) = 'OPEN'::text))
  • Rows Removed by Index Recheck: 985640
  • Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 3)) AND (((fields -> 'sla'::text) ->> 'dueAt'::text) IS NOT NULL) AND ((((fields -> 'assignee'::text) ->> 'agentId'::text) IS NULL) OR (hashed SubPlan 2)))
  • Rows Removed by Filter: 2509
  • Heap Blocks: exact=35456 lossy=122292
5. 15.420 244.912 ↓ 0.0 0 1

BitmapAnd (cost=546.71..546.71 rows=3 width=0) (actual time=244.912..244.912 rows=0 loops=1)

6. 112.354 112.354 ↓ 126.3 744,045 1

Bitmap Index Scan on unique_customer_active_conversation_idx (cost=0.00..185.89 rows=5,893 width=0) (actual time=112.354..112.354 rows=744,045 loops=1)

7. 117.138 117.138 ↓ 159.8 554,181 1

Bitmap Index Scan on conversations_inbox_group_idx (cost=0.00..360.35 rows=3,467 width=0) (actual time=117.137..117.138 rows=554,181 loops=1)

  • Index Cond: ((((fields -> 'assignee'::text) ->> 'routingGroupId'::text) = ANY ('{j0NYS6ogTL2yRaQXLm3yyg,SE2DikRYRNyKZN2qL5LwHA,ujlw9fbaRauxhe2n1Wyr6Q}'::text[])) AND ((fields ->> 'status'::text) = 'OPEN'::text))
8.          

SubPlan (forBitmap Heap Scan)

9. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
10. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.56..7,973.67 rows=1 width=23) (actual time=0.005..0.005 rows=0 loops=1)

11. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on customer_assignees ca (cost=0.00..16.30 rows=630 width=96) (actual time=0.005..0.005 rows=0 loops=1)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using customer_conversations_idx on conversations (cost=0.56..12.62 rows=1 width=614) (never executed)

  • Index Cond: ((fields ->> 'customerId'::text) = ca.id)
  • Filter: ((ca.agent_id = ((fields -> 'assignee'::text) ->> 'agentId'::text)) AND (ca.routing_group_id = ((fields -> 'assignee'::text) ->> 'routingGroupId'::text)) AND ((((fields -> 'sla'::text) ->> 'dueAt'::text))::timestamp with time zone > now()))
13. 0.858 0.858 ↓ 1.0 2,531 1

Index Only Scan using agent_statuses_offline on agent_statuses (cost=0.41..982.85 rows=2,505 width=22) (actual time=0.028..0.858 rows=2,531 loops=1)

  • Heap Fetches: 157
14. 0.023 0.023 ↓ 0.0 0 1

Index Scan using active_chat_conversation_id_external_conversation_id_idx on conversation_items active_realtime (cost=0.50..8.48 rows=1 width=46) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: (c.id = conversation_id)
Planning time : 2.756 ms
Execution time : 802.954 ms