explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l2G1

Settings
# exclusive inclusive rows x rows loops node
1. 72.626 3,258.356 ↓ 1.8 85,246 1

Sort (cost=620,479.54..620,599.39 rows=47,941 width=286) (actual time=3,241.270..3,258.356 rows=85,246 loops=1)

  • Sort Key: concluding_msg.created_at
  • Sort Method: external merge Disk: 22,040kB
2.          

CTE concluding_msg

3. 9.795 451.919 ↓ 1.8 85,247 1

Unique (cost=123,574.18..124,958.98 rows=47,941 width=20) (actual time=434.099..451.919 rows=85,247 loops=1)

4. 34.346 442.124 ↑ 2.8 97,534 1

Sort (cost=123,574.18..124,266.58 rows=276,959 width=20) (actual time=434.099..442.124 rows=97,534 loops=1)

  • Sort Key: tm1.ticket_id, tm1.created_at DESC
  • Sort Method: external merge Disk: 3,248kB
5. 174.350 407.778 ↑ 2.8 97,534 1

Hash Anti Join (cost=38,783.46..92,857.54 rows=276,959 width=20) (actual time=140.137..407.778 rows=97,534 loops=1)

  • Hash Cond: (tm1.ticket_id = tm2.ticket_id)
  • Join Filter: ((tm2.author <> tm1.author) AND (tm2.created_at >= tm1.created_at))
  • Rows Removed by Join Filter: 727,450
6. 93.540 93.540 ↑ 1.0 415,353 1

Seq Scan on ticket_messages tm1 (cost=0.00..31,155.47 rows=415,439 width=20) (actual time=0.012..93.540 rows=415,353 loops=1)

  • Filter: ((deleted_at IS NULL) AND (audience = 'customer'::tcircle))
  • Rows Removed by Filter: 38,365
7. 51.340 139.888 ↑ 1.0 415,353 1

Hash (cost=31,155.47..31,155.47 rows=415,439 width=20) (actual time=139.888..139.888 rows=415,353 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,453kB
8. 88.548 88.548 ↑ 1.0 415,353 1

Seq Scan on ticket_messages tm2 (cost=0.00..31,155.47 rows=415,439 width=20) (actual time=0.003..88.548 rows=415,353 loops=1)

  • Filter: (audience = 'customer'::tcircle)
  • Rows Removed by Filter: 38,365
9.          

CTE ranked_customer_audience_msgs

10. 140.554 373.801 ↑ 1.0 415,353 1

WindowAgg (cost=78,447.32..86,756.10 rows=415,439 width=28) (actual time=195.781..373.801 rows=415,353 loops=1)

11. 145.605 233.247 ↑ 1.0 415,353 1

Sort (cost=78,447.32..79,485.92 rows=415,439 width=20) (actual time=195.774..233.247 rows=415,353 loops=1)

  • Sort Key: ticket_messages.ticket_id, ticket_messages.created_at
  • Sort Method: external merge Disk: 12,200kB
12. 87.642 87.642 ↑ 1.0 415,353 1

Seq Scan on ticket_messages (cost=0.00..31,155.47 rows=415,439 width=20) (actual time=0.009..87.642 rows=415,353 loops=1)

  • Filter: ((deleted_at IS NULL) AND (audience = 'customer'::tcircle))
  • Rows Removed by Filter: 38,365
13.          

CTE non_cust_msgs

14. 43.277 517.305 ↓ 416.3 83,269 1

HashAggregate (cost=13,484.49..13,486.49 rows=200 width=16) (actual time=506.308..517.305 rows=83,269 loops=1)

  • Group Key: ranked_customer_audience_msgs.ticket_id
15. 474.028 474.028 ↑ 1.8 223,354 1

CTE Scan on ranked_customer_audience_msgs (cost=0.00..11,424.57 rows=411,984 width=8) (actual time=195.785..474.028 rows=223,354 loops=1)

  • Filter: ((author <> 'customer'::tcircle) AND ((author <> 'refurbed'::tcircle) OR (rank > 1)))
  • Rows Removed by Filter: 191,999
16. 619.456 3,185.730 ↓ 1.8 85,246 1

Hash Left Join (cost=352,579.00..385,159.81 rows=47,941 width=286) (actual time=2,777.112..3,185.730 rows=85,246 loops=1)

  • Hash Cond: (t.id = non_cust_msgs.ticket_id)
17. 214.702 2,566.274 ↓ 1.8 85,246 1

Nested Loop (cost=352,572.50..383,354.40 rows=47,941 width=287) (actual time=2,234.818..2,566.274 rows=85,246 loops=1)

18. 25.312 2,351.572 ↓ 1.8 85,246 1

Hash Left Join (cost=352,572.07..357,700.30 rows=47,941 width=284) (actual time=2,234.795..2,351.572 rows=85,246 loops=1)

  • Hash Cond: (i.product_id = p.id)
19. 59.691 2,326.260 ↓ 1.8 85,246 1

Hash Join (cost=352,340.17..357,342.18 rows=47,941 width=259) (actual time=2,233.856..2,326.260 rows=85,246 loops=1)

  • Hash Cond: (concluding_msg.ticket_id = t.id)
20. 466.885 466.885 ↓ 1.8 85,247 1

CTE Scan on concluding_msg (cost=0.00..958.82 rows=47,941 width=16) (actual time=434.102..466.885 rows=85,247 loops=1)

21. 34.191 1,799.684 ↑ 1.0 85,246 1

Hash (cost=348,360.58..348,360.58 rows=85,247 width=251) (actual time=1,799.684..1,799.684 rows=85,246 loops=1)

  • Buckets: 16,384 Batches: 8 Memory Usage: 2,826kB
22. 1,248.203 1,765.493 ↑ 1.0 85,246 1

Nested Loop Left Join (cost=60,986.94..348,360.58 rows=85,247 width=251) (actual time=492.681..1,765.493 rows=85,246 loops=1)

  • Join Filter: ((m.id = off.merchant_id) OR ((oi.addon_id IS NOT NULL) AND (m.is_addon_support IS TRUE)))
  • Rows Removed by Join Filter: 11,763,948
23. 0.000 517.290 ↑ 1.0 85,246 1

Gather (cost=60,986.94..170,589.85 rows=85,247 width=243) (actual time=492.617..517.290 rows=85,246 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
24. 24.072 736.263 ↑ 1.2 42,623 2 / 2

Hash Left Join (cost=59,986.94..161,065.15 rows=50,145 width=243) (actual time=490.660..736.263 rows=42,623 loops=2)

  • Hash Cond: (off.instance_id = i.id)
25. 182.078 712.191 ↑ 1.2 42,623 2 / 2

Nested Loop Left Join (cost=59,411.86..160,358.36 rows=50,145 width=211) (actual time=488.475..712.191 rows=42,623 loops=2)

26. 106.052 530.113 ↑ 1.2 42,623 2 / 2

Parallel Hash Join (cost=59,411.43..103,530.42 rows=50,145 width=211) (actual time=488.445..530.113 rows=42,623 loops=2)

  • Hash Cond: (o.user_id = u.id)
27. 161.705 424.061 ↑ 1.2 42,623 2 / 2

Nested Loop (cost=47,724.13..88,029.49 rows=50,145 width=193) (actual time=240.509..424.061 rows=42,623 loops=2)

28. 36.007 262.356 ↑ 1.2 42,623 2 / 2

Parallel Hash Left Join (cost=47,723.70..57,166.42 rows=50,145 width=177) (actual time=240.468..262.356 rows=42,623 loops=2)

  • Hash Cond: (t.assigned_user_id = au.id)
29. 45.688 183.939 ↑ 1.2 42,623 2 / 2

Parallel Hash Join (cost=36,036.41..42,155.49 rows=50,145 width=155) (actual time=148.906..183.939 rows=42,623 loops=2)

  • Hash Cond: (t.order_item_id = oi.id)
30. 8.268 8.268 ↑ 1.2 42,623 2 / 2

Parallel Seq Scan on tickets t (cost=0.00..2,217.45 rows=50,145 width=135) (actual time=0.018..8.268 rows=42,623 loops=2)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 0
31. 51.025 129.983 ↓ 1.2 370,582 2 / 2

Parallel Hash (cost=30,366.18..30,366.18 rows=308,818 width=24) (actual time=129.983..129.983 rows=370,582 loops=2)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,072kB
32. 78.958 78.958 ↓ 1.2 370,582 2 / 2

Parallel Seq Scan on order_items oi (cost=0.00..30,366.18 rows=308,818 width=24) (actual time=0.013..78.958 rows=370,582 loops=2)

33. 22.343 42.410 ↓ 1.2 164,496 2 / 2

Parallel Hash (cost=9,035.80..9,035.80 rows=137,080 width=26) (actual time=42.409..42.410 rows=164,496 loops=2)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,072kB
34. 20.067 20.067 ↓ 1.2 164,496 2 / 2

Parallel Seq Scan on users au (cost=0.00..9,035.80 rows=137,080 width=26) (actual time=0.010..20.067 rows=164,496 loops=2)