explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cw7o

Settings
# exclusive inclusive rows x rows loops node
1. 0.635 53,475.134 ↑ 92.4 19 1

Nested Loop (cost=137,285.96..152,275.56 rows=1,755 width=223) (actual time=53,429.489..53,475.134 rows=19 loops=1)

2.          

CTE conversations_last_take_action

3. 22.380 53,468.687 ↑ 19.6 538 1

Unique (cost=121,988.05..122,040.71 rows=10,532 width=25) (actual time=53,427.546..53,468.687 rows=538 loops=1)

4. 40.343 53,446.307 ↑ 4.1 2,597 1

Sort (cost=121,988.05..122,014.38 rows=10,532 width=25) (actual time=53,427.531..53,446.307 rows=2,597 loops=1)

  • Sort Key: ""conversationTakeEvent"".conversation_id, ""conversationTakeEvent"".""createdAt"" DESC
  • Sort Method: quicksort Memory: 299kB
5. 26,738.158 53,405.964 ↑ 4.1 2,597 1

Hash Join (cost=5,948.78..121,284.38 rows=10,532 width=25) (actual time=52,890.768..53,405.964 rows=2,597 loops=1)

  • Hash Cond: ("conversationTakeEvent".conversation_id = conversation_1.conversation_id)
6. 26,393.419 26,393.419 ↑ 1.4 3,535,689 1

Seq Scan on "conversationTakeEvent" (cost=0.00..96,079.78 rows=5,099,778 width=30) (actual time=0.016..26,393.419 rows=3,535,689 loops=1)

7. 125.989 274.387 ↑ 1.1 16,648 1

Hash (cost=5,721.65..5,721.65 rows=18,170 width=16) (actual time=274.380..274.387 rows=16,648 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1037kB
8. 148.398 148.398 ↑ 1.1 16,648 1

Index Scan using "conversation_closedAt_idx" on conversation conversation_1 (cost=0.43..5,721.65 rows=18,170 width=16) (actual time=0.030..148.398 rows=16,648 loops=1)

  • Index Cond: ("closedAt" IS NULL)
  • Filter: ((project_id <> 'eewnswogqhbiufgvyijpmts-72'::text) AND (type <> 'MobileAppConversation'::text))
  • Rows Removed by Filter: 8
9.          

CTE old_state

10. 0.519 53,473.907 ↑ 92.4 19 1

Nested Loop (cost=0.56..15,244.69 rows=1,755 width=35) (actual time=53,429.386..53,473.907 rows=19 loops=1)

11. 53,472.818 53,472.818 ↑ 92.4 19 1

CTE Scan on conversations_last_take_action (cost=0.00..289.63 rows=1,755 width=25) (actual time=53,429.336..53,472.818 rows=19 loops=1)

  • Filter: ("isTaken" AND ("createdAt" < (now() - '00:05:00'::interval)))
  • Rows Removed by Filter: 519
12. 0.570 0.570 ↑ 1.0 1 19

Index Scan using conversation_conversation_id_pkey on conversation conversation_2 (cost=0.56..8.51 rows=1 width=26) (actual time=0.023..0.030 rows=1 loops=19)

  • Index Cond: (conversation_id = conversations_last_take_action.conversation_id)
13. 53,474.195 53,474.195 ↑ 92.4 19 1

CTE Scan on old_state (cost=0.00..35.10 rows=1,755 width=57) (actual time=53,429.403..53,474.195 rows=19 loops=1)

14. 0.304 0.304 ↑ 1.0 1 19

Index Scan using conversation_conversation_id_pkey on conversation (cost=0.56..8.51 rows=1 width=166) (actual time=0.009..0.016 rows=1 loops=19)

  • Index Cond: (conversation_id = old_state.conversation_id)