explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7anU

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Append (cost=60,622.55..60,639.38 rows=2 width=100) (never executed)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=60,622.55..60,622.64 rows=1 width=100) (never executed)

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=60,622.55..60,622.62 rows=1 width=96) (never executed)

4.          

CTE historical_summary

5. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.62..8.63 rows=1 width=68) (never executed)

6. 0.000 0.000 ↓ 0.0

Unique (cost=8.59..8.6 rows=1 width=113) (never executed)

7. 0.000 0.000 ↓ 0.0

Sort (cost=8.59..8.59 rows=1 width=113) (never executed)

  • Sort Key: balance_summary.blocked, balance_summary.date_summary DESC
8. 0.000 0.000 ↓ 0.0

Index Scan using balance_summary_org_id_recipient_id_date_d579e17c_uniq on balance_summary balance_summary (cost=0.42..8.58 rows=1 width=113) (never executed)

  • Index Cond: ((org_id = 9) AND (recipient_id = 156) AND ((status)::text = 'paid'::text) AND (blocked IS NULL))
9.          

CTE today_balance_summary

10. 0.000 0.000 ↓ 0.0

Aggregate (cost=60,613.91..60,613.92 rows=1 width=104) (never executed)

11. 0.000 0.000 ↓ 0.0

Sort (cost=60,591.05..60,592 rows=381 width=927) (never executed)

  • Sort Key: balance_operation.created_at DESC
12. 0.000 0.000 ↓ 0.0

Append (cost=21,177.66..60,574.72 rows=381 width=927) (never executed)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=21,177.66..53,444.44 rows=364 width=637) (never executed)

14. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,177.38..53,314.15 rows=364 width=534) (never executed)

15. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,158.83..53,291.68 rows=364 width=456) (never executed)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=21,157.76..53,285.63 rows=364 width=446) (never executed)

17. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,157.47..53,115.11 rows=364 width=375) (never executed)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=21,147.2..53,103.46 rows=364 width=343) (never executed)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..9.22 rows=1 width=97) (never executed)

20. 0.000 0.000 ↓ 0.0

Index Scan using recipient_pkey on recipient recipient (cost=0.14..8.16 rows=1 width=66) (never executed)

  • Index Cond: (id = 156)
  • Filter: (org_id = 9)
21. 0.000 0.000 ↓ 0.0

Seq Scan on organization organization (cost=0..1.05 rows=1 width=39) (never executed)

  • Filter: (id = 9)
22. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,147.05..53,090.6 rows=364 width=254) (never executed)

  • Filter: ((event_type.id IS NULL) OR (((event_type.reference_key)::text = 'anticipation_spread'::text) AND (payable.amount > 0)) OR (((event_type.reference_key)::text = 'anticipation_spread'::text) AND ((balance_operation.operation_type)::text = 'event'::text)) OR ((event_type.reference_key)::text <> 'anticipation_spread'::text))
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=21,144.15..53,077.91 rows=373 width=225) (never executed)

24. 0.000 0.000 ↓ 0.0

Hash Join (cost=21,143.87..52,954.68 rows=373 width=190) (never executed)

25. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on balance_operation balance_operation (cost=5,176.12..35,949.34 rows=4,496 width=51) (never executed)

  • Filter: ((created_at >= '2020-01-09 00:00:00-03'::timestamp with time zone) AND (created_at <= '2020-01-09 23:59:59-03'::timestamp with time zone) AND ((operation_type)::text <> 'transfer'::text) AND ((type)::text = 'update_status'::text) AND ((operation_origin)::text = 'target'::text))
26. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on balance_operation_status_fbb224e1_like (cost=0..5,174.99 rows=185,409 width=0) (never executed)

  • Index Cond: ((status)::text = 'paid'::text)
27. 0.000 0.000 ↓ 0.0

Hash (cost=14,498.55..14,498.55 rows=43,216 width=147) (never executed)

28. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on payable payable (cost=1,555.35..14,498.55 rows=43,216 width=147) (never executed)

29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on payable_recipient_id_2fd4c012 (cost=0..1,544.54 rows=43,216 width=0) (never executed)

  • Index Cond: (recipient_id = 156)
30. 0.000 0.000 ↓ 0.0

Index Scan using event_pkey on event event (cost=0.29..0.32 rows=1 width=39) (never executed)

  • Index Cond: (payable.event_id = id)
31. 0.000 0.000 ↓ 0.0

Hash (cost=2.4..2.4 rows=40 width=59) (never executed)

32. 0.000 0.000 ↓ 0.0

Seq Scan on event_type event_type (cost=0..2.4 rows=40 width=59) (never executed)

33. 0.000 0.000 ↓ 0.0

Hash (cost=7.9..7.9 rows=190 width=36) (never executed)

34. 0.000 0.000 ↓ 0.0

Seq Scan on refund refund (cost=0..7.9 rows=190 width=36) (never executed)

35. 0.000 0.000 ↓ 0.0

Index Scan using transaction_pkey on transaction transaction (cost=0.29..0.46 rows=1 width=75) (never executed)

  • Index Cond: (payable.transaction_id = id)
36. 0.000 0.000 ↓ 0.0

Hash (cost=1.03..1.03 rows=3 width=18) (never executed)

37. 0.000 0.000 ↓ 0.0

Seq Scan on payment_method payment_method (cost=0..1.03 rows=3 width=18) (never executed)

38. 0.000 0.000 ↓ 0.0

Hash (cost=13.8..13.8 rows=380 width=86) (never executed)

39. 0.000 0.000 ↓ 0.0

Seq Scan on card_brand card_brand (cost=0..13.8 rows=380 width=86) (never executed)

40. 0.000 0.000 ↓ 0.0

Index Scan using order_pkey on order order (cost=0.29..0.33 rows=1 width=33) (never executed)

  • Index Cond: (id = transaction.order_id)
41. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=7,125.15..7,126.63 rows=17 width=587) (never executed)

42. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=7,125.15..7,126.46 rows=17 width=595) (never executed)

  • Group Key: payable_1.transfer_id, recipient_1.public_id, recipient_1.reference_key, recipient_1.name, organization_1.public_id, organization_1.reference_key, organization_1.name, transfer.public_id, transfer.external_transfer_id, transfer.fee, transfer.transferred_amount, transfer.reference_key, transfer.metadata, payable_1.status, transaction_1.public_id
43. 0.000 0.000 ↓ 0.0

Sort (cost=7,125.15..7,125.19 rows=17 width=246) (never executed)

  • Sort Key: payable_1.transfer_id, recipient_1.public_id, recipient_1.reference_key, recipient_1.name, organization_1.public_id, organization_1.reference_key, organization_1.name, transfer.public_id, transfer.external_transfer_id, transfer.fee, transfer.transferred_amount, transfer.reference_key, transfer.metadata, payable_1.status, transaction_1.public_id
44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=50.21..7,124.8 rows=17 width=246) (never executed)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=49.92..7,116.83 rows=17 width=234) (never executed)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=49.78..7,113.94 rows=17 width=137) (never executed)

47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..9.22 rows=1 width=97) (never executed)

48. 0.000 0.000 ↓ 0.0

Index Scan using recipient_pkey on recipient recipient_1 (cost=0.14..8.16 rows=1 width=66) (never executed)

  • Index Cond: (id = 156)
  • Filter: (org_id = 9)
49. 0.000 0.000 ↓ 0.0

Seq Scan on organization organization_1 (cost=0..1.05 rows=1 width=39) (never executed)

  • Filter: (id = 9)
50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=49.64..7,104.54 rows=17 width=48) (never executed)

51. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on balance_operation balance_operation_1 (cost=49.21..5,425.14 rows=202 width=24) (never executed)

  • Filter: ((((type)::text = 'update_status'::text) OR (type IS NULL)) AND (created_at >= '2020-01-09 00:00:00-03'::timestamp with time zone) AND (created_at <= '2020-01-09 23:59:59-03'::timestamp with time zone))
52. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on balance_operation_status_fbb224e1_like (cost=0..49.16 rows=1,698 width=0) (never executed)

  • Index Cond: ((status)::text = 'transferred'::text)
53. 0.000 0.000 ↓ 0.0

Index Scan using payable_pkey on payable payable_1 (cost=0.42..8.3 rows=1 width=36) (never executed)

  • Index Cond: (id = balance_operation_1.payable_id)
  • Filter: (recipient_id = 156)
54. 0.000 0.000 ↓ 0.0

Index Scan using transfer_pkey on transfer transfer (cost=0.14..0.16 rows=1 width=101) (never executed)

  • Index Cond: (payable_1.transfer_id = id)
55. 0.000 0.000 ↓ 0.0

Index Scan using transaction_pkey on transaction transaction_1 (cost=0.29..0.46 rows=1 width=24) (never executed)

  • Index Cond: (payable_1.transaction_id = id)
56. 0.000 0.000 ↓ 0.0

CTE Scan on today_balance_summary today_balance_summary (cost=0..0.02 rows=1 width=104) (never executed)

  • Filter: (quantity_operations > 0)
57. 0.000 0.000 ↓ 0.0

CTE Scan on historical_summary hs (cost=0..0.02 rows=1 width=68) (never executed)

58. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.56..16.74 rows=1 width=100) (never executed)

  • Group Key: bs.date_summary
  • Filter: (sum(bs.quantity_operations) > 0)
59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..16.66 rows=1 width=76) (never executed)

60. 0.000 0.000 ↓ 0.0

Index Scan using balance_summary_org_id_recipient_id_date_d579e17c_uniq on balance_summary bs (cost=0.42..8.49 rows=1 width=80) (never executed)

  • Index Cond: ((org_id = 9) AND (recipient_id = 156) AND (date_summary >= '2019-12-25'::date) AND (date_summary <= '2020-01-09'::date) AND ((status)::text = 'paid'::text) AND (blocked IS NULL))
61. 0.000 0.000 ↓ 0.0

Index Only Scan using recipient_pkey on recipient r (cost=0.14..8.16 rows=1 width=4) (never executed)

  • Index Cond: (id = 156)