explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2tKq

Settings
# exclusive inclusive rows x rows loops node
1. 0.557 53,948.118 ↑ 109.7 16 1

Nested Loop (cost=208,651.44..223,913.00 rows=1,755 width=223) (actual time=53,867.068..53,948.118 rows=16 loops=1)

2.          

CTE conversations_last_take_action

3. 23.161 53,906.350 ↑ 19.5 539 1

Unique (cost=121,942.81..121,995.48 rows=10,534 width=25) (actual time=53,863.944..53,906.350 rows=539 loops=1)

4. 41.203 53,883.189 ↑ 4.0 2,614 1

Sort (cost=121,942.81..121,969.14 rows=10,534 width=25) (actual time=53,863.929..53,883.189 rows=2,614 loops=1)

  • Sort Key: ""conversationTakeEvent"".conversation_id, ""conversationTakeEvent"".""createdAt"" DESC
  • Sort Method: quicksort Memory: 301kB
5. 26,973.872 53,841.986 ↑ 4.0 2,614 1

Hash Join (cost=5,903.37..121,238.99 rows=10,534 width=25) (actual time=53,326.223..53,841.986 rows=2,614 loops=1)

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

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

7. 138.511 305.318 ↑ 1.1 16,634 1

Hash (cost=5,676.19..5,676.19 rows=18,174 width=16) (actual time=305.311..305.318 rows=16,634 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,036kB
8. 166.807 166.807 ↑ 1.1 16,634 1

Index Scan using "conversation_closedAt_idx" on conversation conversation_1 (cost=0.43..5,676.19 rows=18,174 width=16) (actual time=0.034..166.807 rows=16,634 loops=1)

  • Index Cond: ("closedAt" IS NULL)
  • Filter: (project_id <> 'eewnswogqhbiufgvyijpmts-72'::text)
9.          

CTE old_state

10. 15.837 53,942.942 ↑ 19.5 539 1

Nested Loop (cost=0.56..86,655.40 rows=10,534 width=35) (actual time=53,864.016..53,942.942 rows=539 loops=1)

11. 53,914.708 53,914.708 ↑ 19.5 539 1

CTE Scan on conversations_last_take_action (cost=0.00..210.68 rows=10,534 width=25) (actual time=53,863.960..53,914.708 rows=539 loops=1)

12. 12.397 12.397 ↑ 1.0 1 539

Index Scan using conversation_conversation_id_pkey on conversation conversation_2 (cost=0.56..8.20 rows=1 width=26) (actual time=0.015..0.023 rows=1 loops=539)

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

CTE Scan on old_state (cost=0.00..289.69 rows=1,756 width=57) (actual time=53,867.030..53,947.162 rows=19 loops=1)

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

Index Scan using conversation_conversation_id_pkey on conversation (cost=0.56..8.52 rows=1 width=166) (actual time=0.014..0.021 rows=1 loops=19)

  • Index Cond: (conversation_id = old_state.conversation_id)
  • Filter: ((type <> 'MobileAppConversation'::text) AND (project_id <> 'eewnswogqhbiufgvyijpmts-72'::text))
  • Rows Removed by Filter: 0