explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QV3D : with only true index

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 6.814 ↓ 14.0 14 1

Limit (cost=122.80..122.81 rows=1 width=366) (actual time=6.813..6.814 rows=14 loops=1)

2.          

CTE selection

3. 0.028 3.908 ↓ 14.0 14 1

Nested Loop (cost=38.04..91.68 rows=1 width=280) (actual time=1.132..3.908 rows=14 loops=1)

4. 0.009 3.838 ↓ 14.0 14 1

Nested Loop Left Join (cost=37.61..91.14 rows=1 width=279) (actual time=1.124..3.838 rows=14 loops=1)

5. 0.013 3.801 ↓ 14.0 14 1

Nested Loop Left Join (cost=37.34..90.50 rows=1 width=254) (actual time=1.118..3.801 rows=14 loops=1)

6. 0.492 3.760 ↓ 14.0 14 1

Nested Loop Left Join (cost=37.05..90.19 rows=1 width=222) (actual time=1.113..3.760 rows=14 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: 1,932
7. 0.045 3.030 ↓ 14.0 14 1

Nested Loop (cost=37.05..58.06 rows=1 width=214) (actual time=1.071..3.030 rows=14 loops=1)

8. 0.105 2.583 ↓ 134.0 134 1

Nested Loop Left Join (cost=36.62..56.92 rows=1 width=214) (actual time=1.063..2.583 rows=134 loops=1)

9. 0.105 2.344 ↓ 134.0 134 1

Nested Loop (cost=36.20..48.48 rows=1 width=192) (actual time=1.059..2.344 rows=134 loops=1)

10. 0.007 1.971 ↓ 134.0 134 1

Nested Loop (cost=35.78..48.00 rows=1 width=174) (actual time=1.054..1.971 rows=134 loops=1)

11. 0.135 1.562 ↓ 134.0 134 1

Nested Loop (cost=35.35..47.38 rows=1 width=155) (actual time=1.048..1.562 rows=134 loops=1)

12. 0.130 1.159 ↓ 134.0 134 1

Bitmap Heap Scan on tickets t_1 (cost=34.93..38.94 rows=1 width=135) (actual time=1.039..1.159 rows=134 loops=1)

  • Recheck Cond: ((status ~ 'unresolved.*'::lquery) AND (expedite IS TRUE))
  • Filter: (deleted_at IS NULL)
  • Heap Blocks: exact=89
13. 0.041 1.029 ↓ 0.0 0 1

BitmapAnd (cost=34.93..34.93 rows=1 width=0) (actual time=1.029..1.029 rows=0 loops=1)

14. 0.926 0.926 ↓ 92.1 7,827 1

Bitmap Index Scan on tickets_status_idx (cost=0.00..8.92 rows=85 width=0) (actual time=0.926..0.926 rows=7,827 loops=1)

  • Index Cond: (status ~ 'unresolved.*'::lquery)
15. 0.062 0.062 ↑ 1.1 1,096 1

Bitmap Index Scan on tickets_expedite_idx (cost=0.00..25.76 rows=1,179 width=0) (actual time=0.062..0.062 rows=1,096 loops=1)

16. 0.268 0.268 ↑ 1.0 1 134

Index Scan using order_items_pk on order_items oi (cost=0.42..8.44 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=134)

  • Index Cond: (id = t_1.order_item_id)
17. 0.402 0.402 ↑ 1.0 1 134

Index Scan using orders_pk on orders o (cost=0.42..0.62 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=134)

  • Index Cond: (id = oi.order_id)
18. 0.268 0.268 ↑ 1.0 1 134

Index Scan using users_pk on users u (cost=0.42..0.49 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=134)

  • Index Cond: (id = o.user_id)
19. 0.134 0.134 ↑ 1.0 1 134

Index Scan using users_pk on users au (cost=0.42..8.44 rows=1 width=26) (actual time=0.001..0.001 rows=1 loops=134)

  • Index Cond: (id = t_1.assigned_user_id)
20. 0.402 0.402 ↓ 0.0 0 134

Index Scan using offers_pk on offers off (cost=0.43..1.14 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=134)

  • Index Cond: ((id = oi.offer_id) AND (valid_from = oi.offer_valid_from))
  • Filter: (merchant_id = 8)
  • Rows Removed by Filter: 1
21. 0.238 0.238 ↑ 1.0 139 14

Seq Scan on merchants m (cost=0.00..30.39 rows=139 width=17) (actual time=0.001..0.017 rows=139 loops=14)

22. 0.028 0.028 ↑ 1.0 1 14

Index Scan using instances_pk on instances i (cost=0.28..0.32 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (id = off.instance_id)
23. 0.028 0.028 ↑ 1.0 1 14

Index Scan using products_pk on products p (cost=0.28..0.63 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (id = i.product_id)
24. 0.042 0.042 ↑ 1.0 1 14

Index Scan using addresses_pk on addresses ad (cost=0.42..0.54 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=14)

  • Index Cond: ((id = o.invoice_addr_id) AND (valid_from = o.invoice_addr_valid_from))
25.          

CTE concluding_msg

26. 0.003 6.203 ↓ 2.3 14 1

Unique (cost=18.67..18.70 rows=6 width=20) (actual time=6.200..6.203 rows=14 loops=1)

27. 0.009 6.200 ↓ 3.0 18 1

Sort (cost=18.67..18.68 rows=6 width=20) (actual time=6.199..6.200 rows=18 loops=1)

  • Sort Key: tm1.ticket_id, tm1.created_at DESC
  • Sort Method: quicksort Memory: 26kB
28. 0.091 6.191 ↓ 3.0 18 1

Nested Loop Anti Join (cost=0.84..18.59 rows=6 width=20) (actual time=1.252..6.191 rows=18 loops=1)

29. 0.025 4.245 ↓ 29.4 265 1

Nested Loop (cost=0.42..11.37 rows=9 width=20) (actual time=1.140..4.245 rows=265 loops=1)

30. 3.926 3.926 ↓ 14.0 14 1

CTE Scan on selection t_2 (cost=0.00..0.02 rows=1 width=8) (actual time=1.135..3.926 rows=14 loops=1)

31. 0.294 0.294 ↓ 2.1 19 14

Index Scan using ticket_messages_ticket_id_idx on ticket_messages tm1 (cost=0.42..11.26 rows=9 width=20) (actual time=0.004..0.021 rows=19 loops=14)

  • Index Cond: (ticket_id = t_2.id)
  • Filter: ((deleted_at IS NULL) AND (audience = 'customer'::tcircle))
  • Rows Removed by Filter: 17
32. 1.855 1.855 ↑ 2.0 1 265

Index Scan using ticket_messages_ticket_id_idx on ticket_messages tm2 (cost=0.42..0.91 rows=2 width=20) (actual time=0.007..0.007 rows=1 loops=265)

  • Index Cond: (ticket_id = tm1.ticket_id)
  • Filter: ((audience = 'customer'::tcircle) AND (author <> tm1.author) AND (created_at >= tm1.created_at))
  • Rows Removed by Filter: 16
33.          

CTE ranked_customer_audience_msgs

34. 0.113 0.472 ↓ 29.4 265 1

WindowAgg (cost=11.51..11.69 rows=9 width=28) (actual time=0.354..0.472 rows=265 loops=1)

35. 0.067 0.359 ↓ 29.4 265 1

Sort (cost=11.51..11.54 rows=9 width=20) (actual time=0.349..0.359 rows=265 loops=1)

  • Sort Key: ticket_messages.ticket_id, ticket_messages.created_at
  • Sort Method: quicksort Memory: 45kB
36. 0.024 0.292 ↓ 29.4 265 1

Nested Loop (cost=0.42..11.37 rows=9 width=20) (actual time=0.005..0.292 rows=265 loops=1)

37. 0.002 0.002 ↓ 14.0 14 1

CTE Scan on selection t_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.002 rows=14 loops=1)

38. 0.266 0.266 ↓ 2.1 19 14

Index Scan using ticket_messages_ticket_id_idx on ticket_messages (cost=0.42..11.26 rows=9 width=20) (actual time=0.003..0.019 rows=19 loops=14)

  • Index Cond: (ticket_id = t_3.id)
  • Filter: ((deleted_at IS NULL) AND (audience = 'customer'::tcircle))
  • Rows Removed by Filter: 17
39.          

CTE non_cust_msgs

40. 0.025 0.553 ↓ 2.0 14 1

HashAggregate (cost=0.28..0.35 rows=7 width=16) (actual time=0.552..0.553 rows=14 loops=1)

  • Group Key: ranked_customer_audience_msgs.ticket_id
41. 0.528 0.528 ↓ 23.4 164 1

CTE Scan on ranked_customer_audience_msgs (cost=0.00..0.25 rows=7 width=8) (actual time=0.357..0.528 rows=164 loops=1)

  • Filter: ((author <> 'customer'::tcircle) AND ((author <> 'refurbed'::tcircle) OR (rank > 1)))
  • Rows Removed by Filter: 101
42. 0.011 6.812 ↓ 14.0 14 1

Sort (cost=0.38..0.39 rows=1 width=366) (actual time=6.812..6.812 rows=14 loops=1)

  • Sort Key: concluding_msg.created_at
  • Sort Method: quicksort Memory: 32kB
43. 0.017 6.801 ↓ 14.0 14 1

Hash Right Join (cost=0.20..0.37 rows=1 width=366) (actual time=6.784..6.801 rows=14 loops=1)

  • Hash Cond: (non_cust_msgs.ticket_id = t.id)
44. 0.556 0.556 ↓ 2.0 14 1

CTE Scan on non_cust_msgs (cost=0.00..0.14 rows=7 width=16) (actual time=0.552..0.556 rows=14 loops=1)

45. 0.007 6.228 ↓ 14.0 14 1

Hash (cost=0.19..0.19 rows=1 width=365) (actual time=6.228..6.228 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
46. 0.013 6.221 ↓ 14.0 14 1

Hash Join (cost=0.03..0.19 rows=1 width=365) (actual time=6.212..6.221 rows=14 loops=1)

  • Hash Cond: (concluding_msg.ticket_id = t.id)
47. 6.205 6.205 ↓ 2.3 14 1

CTE Scan on concluding_msg (cost=0.00..0.12 rows=6 width=16) (actual time=6.201..6.205 rows=14 loops=1)

48. 0.002 0.003 ↓ 14.0 14 1

Hash (cost=0.02..0.02 rows=1 width=357) (actual time=0.003..0.003 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
49. 0.001 0.001 ↓ 14.0 14 1

CTE Scan on selection t (cost=0.00..0.02 rows=1 width=357) (actual time=0.000..0.001 rows=14 loops=1)

Planning time : 2.245 ms
Execution time : 6.959 ms