explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZjWd

Settings
# exclusive inclusive rows x rows loops node
1. 5.446 12,159.719 ↑ 1.0 200 1

Limit (cost=2,501,736.15..2,501,736.65 rows=200 width=9,660) (actual time=12,159.351..12,159.719 rows=200 loops=1)

2. 519.876 12,154.273 ↑ 1.9 30,200 1

Sort (cost=2,501,661.15..2,501,801.18 rows=56,012 width=9,660) (actual time=12,123.481..12,154.273 rows=30,200 loops=1)

  • Sort Key: tx.updated_at, tx.id DESC
  • Sort Method: external merge Disk: 117688kB
3. 73.547 11,634.397 ↓ 1.2 64,511 1

Nested Loop Left Join (cost=1,470,976.61..2,033,556.64 rows=56,012 width=9,660) (actual time=6,661.447..11,634.397 rows=64,511 loops=1)

4. 74.061 11,302.806 ↓ 1.2 64,511 1

Nested Loop Left Join (cost=1,470,976.34..2,016,521.01 rows=56,012 width=9,640) (actual time=6,661.433..11,302.806 rows=64,511 loops=1)

5. 126.654 10,777.168 ↓ 1.2 64,511 1

Nested Loop Left Join (cost=1,470,976.05..1,997,922.26 rows=56,012 width=9,611) (actual time=6,661.413..10,777.168 rows=64,511 loops=1)

6. 105.344 10,327.959 ↓ 1.2 64,511 1

Nested Loop Left Join (cost=1,470,975.78..1,980,384.18 rows=56,012 width=9,611) (actual time=6,661.394..10,327.959 rows=64,511 loops=1)

7. 73.558 9,642.016 ↓ 1.2 64,511 1

Hash Left Join (cost=1,470,975.35..1,825,646.78 rows=56,012 width=9,550) (actual time=6,661.368..9,642.016 rows=64,511 loops=1)

  • Hash Cond: ((tx.id)::text = (ccce_txn_mapping.transaction_id)::text)
8. 77.966 9,551.118 ↓ 1.2 64,511 1

Hash Left Join (cost=1,469,577.39..1,823,478.65 rows=56,012 width=9,535) (actual time=6,643.911..9,551.118 rows=64,511 loops=1)

  • Hash Cond: ((tx.cost_center_id = cc.id) AND ((ou.org_id)::text = (cc.org_id)::text))
9. 57.731 9,458.073 ↓ 1.2 64,511 1

Hash Left Join (cost=1,468,307.12..1,821,780.08 rows=56,012 width=9,518) (actual time=6,628.705..9,458.073 rows=64,511 loops=1)

  • Hash Cond: (tx.project_id = pj.id)
10. 50.915 9,380.274 ↓ 1.2 64,511 1

Nested Loop Left Join (cost=1,464,317.95..1,817,020.74 rows=56,012 width=9,476) (actual time=6,608.592..9,380.274 rows=64,511 loops=1)

11. 76.104 9,200.337 ↓ 1.2 64,511 1

Nested Loop Left Join (cost=1,464,317.52..1,758,283.34 rows=56,012 width=9,458) (actual time=6,608.587..9,200.337 rows=64,511 loops=1)

12. 67.536 8,543.634 ↓ 1.2 64,511 1

Nested Loop (cost=1,464,317.10..1,702,781.97 rows=56,012 width=9,421) (actual time=6,608.566..8,543.634 rows=64,511 loops=1)

13. 70.991 8,282.565 ↓ 1.2 64,511 1

Hash Join (cost=1,464,316.68..1,601,160.54 rows=56,012 width=9,389) (actual time=6,608.553..8,282.565 rows=64,511 loops=1)

  • Hash Cond: ((ou.org_id)::text = (o.id)::text)
14. 114.142 8,183.931 ↓ 1.2 64,511 1

Nested Loop (cost=1,461,963.33..1,598,037.03 rows=56,012 width=9,365) (actual time=6,580.829..8,183.931 rows=64,511 loops=1)

15. 133.256 7,618.212 ↓ 1.2 64,511 1

Nested Loop (cost=1,461,962.92..1,549,744.46 rows=56,012 width=9,319) (actual time=6,580.811..7,618.212 rows=64,511 loops=1)

16. 182.595 6,968.868 ↓ 1.2 64,511 1

Unique (cost=1,461,962.50..1,482,967.00 rows=56,012 width=5,051) (actual time=6,580.775..6,968.868 rows=64,511 loops=1)

17. 610.498 6,786.273 ↓ 1.2 64,511 1

Sort (cost=1,461,962.50..1,462,102.53 rows=56,012 width=5,051) (actual time=6,580.772..6,786.273 rows=64,511 loops=1)

  • Sort Key: tx.id, tx.org_user_id, tx.txn_dt, tx.created_at, tx.category, tx.amount, tx.currency, tx.report_id, tx.status_id, tx.tax, tx.state, tx.updated_at, tx.source, tx.num_files, tx.invoice_number, tx.purpose, tx.vendor_id, tx.project_id, tx.billable, tx.skip_reimbursement, tx.orig_amount, tx.orig_currency, tx.creator_id, tx.custom_attributes, tx.cost_center_id, tx.external_id, tx.platform_vendor_id, tx.verification_state, tx.payment_id, tx.source_account_id, tx.last_updated_by, tx.transcription_state, tx.physical_bill, tx.org_category_id, tx.user_amount, tx.policy_amount, tx.admin_amount, tx.policy_state, tx.manual_flag, tx.policy_flag, tx.expense_number, tx.extracted_data, tx.split_group_id, tx.split_group_user_amount, tx.location1, tx.location2, tx.location3, tx.location4, tx.location5, tx.location6, tx.location7, tx.location8, tx.location9, tx.location10, tx.distance, tx.distance_unit, tx.from_dt, tx.to_dt, tx.num_days, tx.mileage_calculated_distance, tx.mileage_calculated_amount, tx.mileage_vehicle_type, tx.mileage_rate, tx.mileage_is_round_trip, tx.hotel_is_breakfast_provided, tx.flight_journey_travel_class, tx.flight_return_travel_class, tx.train_travel_class, tx.bus_travel_class, tx.per_diem_rate_id, tx.activity_policy_pending, tx.activity_details, tx.text_column1, tx.text_column2, tx.text_column3, tx.text_column4, tx.text_column5, tx.text_column6, tx.text_column7, tx.text_column8, tx.text_column9, tx.text_column10, tx.text_column11, tx.text_column12, tx.text_column13, tx.text_column14, tx.text_column15, tx.text_array_column1, tx.text_array_column2, tx.text_array_column3, tx.text_array_column4, tx.text_array_column5, tx.text_array_column6, tx.text_array_column7, tx.text_array_column8, tx.text_array_column9, tx.text_array_column10, tx.decimal_column1, tx.decimal_column2, tx.decimal_column3, tx.decimal_column4, tx.decimal_column5, tx.decimal_column6, tx.decimal_column7, tx.decimal_column8, tx.decimal_column9, tx.decimal_column10, tx.location_column1, tx.location_column2, tx.location_column3, tx.location_column4, tx.location_column5, tx.location_column6, tx.location_column7, tx.location_column8, tx.location_column9, tx.location_column10, tx.boolean_column1, tx.boolean_column2, tx.boolean_column3, tx.boolean_column4, tx.boolean_column5, tx.boolean_column6, tx.boolean_column7, tx.boolean_column8, tx.boolean_column9, tx.boolean_column10, tx.timestamp_column1, tx.timestamp_column2, tx.timestamp_column3, tx.timestamp_column4, tx.timestamp_column5, tx.timestamp_column6, tx.timestamp_column7, tx.timestamp_column8, tx.timestamp_column9, tx.timestamp_column10, tx.fyle_category, tx.proposed_exchange_rate, tx.exchange_rate, tx.exchange_rate_diff_percentage, tx.custom_properties, tx.physical_bill_at, tx.transcribed_data, tx.user_review_needed, tx.mandatory_fields_present, tx.user_can_delete, tx.user_reason_for_duplicate_expenses, ((SubPlan 13))
  • Sort Method: external merge Disk: 97208kB
18. 25.931 6,175.775 ↓ 1.2 64,511 1

Append (cost=3,105.42..1,214,399.99 rows=56,012 width=5,051) (actual time=6,088.081..6,175.775 rows=64,511 loops=1)

19. 0.003 3.831 ↓ 0.0 0 1

Sort (cost=3,105.42..3,105.43 rows=1 width=5,038) (actual time=3.831..3.831 rows=0 loops=1)

  • Sort Key: tx.created_at
  • Sort Method: quicksort Memory: 25kB
20. 0.579 3.828 ↓ 0.0 0 1

Index Scan using idx_transactions_org_user_id on transactions tx (cost=0.43..3,105.41 rows=1 width=5,038) (actual time=3.828..3.828 rows=0 loops=1)

  • Index Cond: ((org_user_id)::text = 'oukRlv4I8fk3'::text)
  • Filter: ((SubPlan 14) ~~ '%oukRlv4I8fk3%'::text)
  • Rows Removed by Filter: 361
21.          

SubPlan (for Index Scan)

22. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_13 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx.report_id)::text)
24. 0.361 3.249 ↑ 1.0 1 361

Aggregate (cost=12.46..12.47 rows=1 width=13) (actual time=0.009..0.009 rows=1 loops=361)

25. 2.888 2.888 ↑ 2.0 1 361

Index Scan using idx_report_approvals_report_id on report_approvals ra_14 (cost=0.42..12.46 rows=2 width=13) (actual time=0.007..0.008 rows=1 loops=361)

  • Index Cond: ((report_id)::text = (tx.report_id)::text)
26. 0.003 0.021 ↓ 0.0 0 1

Sort (cost=136,763.33..136,763.33 rows=1 width=5,038) (actual time=0.021..0.021 rows=0 loops=1)

  • Sort Key: tx_1.created_at
  • Sort Method: quicksort Memory: 25kB
27. 0.000 0.018 ↓ 0.0 0 1

Nested Loop (cost=9.12..136,763.32 rows=1 width=5,038) (actual time=0.018..0.018 rows=0 loops=1)

28. 0.002 0.018 ↓ 0.0 0 1

Bitmap Heap Scan on projects p (cost=8.69..65.97 rows=15 width=4) (actual time=0.018..0.018 rows=0 loops=1)

  • Recheck Cond: (((approver1_id)::text = 'oukRlv4I8fk3'::text) OR ((approver2_id)::text = 'oukRlv4I8fk3'::text))
29. 0.001 0.016 ↓ 0.0 0 1

BitmapOr (cost=8.69..8.69 rows=15 width=0) (actual time=0.016..0.016 rows=0 loops=1)

30. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on idx_projects_approver1_id (cost=0.00..4.35 rows=9 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((approver1_id)::text = 'oukRlv4I8fk3'::text)
31. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on idx_projects_approver2_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((approver2_id)::text = 'oukRlv4I8fk3'::text)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_transactions_project_id on transactions tx_1 (cost=0.43..9,112.32 rows=1 width=5,038) (never executed)

  • Index Cond: (project_id = p.id)
  • Filter: ((SubPlan 12) ~~ '%oukRlv4I8fk3%'::text)
33.          

SubPlan (for Index Scan)

34. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_12 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_1.report_id)::text)
36.          

SubPlan (for Nested Loop)

37. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_11 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_1.report_id)::text)
39. 0.001 1.153 ↓ 0.0 0 1

Sort (cost=20,934.59..20,934.60 rows=1 width=5,038) (actual time=1.153..1.153 rows=0 loops=1)

  • Sort Key: tx_2.created_at
  • Sort Method: quicksort Memory: 25kB
40. 0.002 1.152 ↓ 0.0 0 1

Nested Loop (cost=5.02..20,934.58 rows=1 width=5,038) (actual time=1.152..1.152 rows=0 loops=1)

41. 0.000 1.150 ↓ 0.0 0 1

Nested Loop (cost=4.59..623.18 rows=8 width=13) (actual time=1.150..1.150 rows=0 loops=1)

42. 1.150 1.150 ↓ 0.0 0 1

Seq Scan on departments d (cost=0.00..533.29 rows=1 width=15) (actual time=1.150..1.150 rows=0 loops=1)

  • Filter: ((department_head_id)::text = 'oukRlv4I8fk3'::text)
  • Rows Removed by Filter: 8759
43. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on org_users ou_1 (cost=4.59..89.67 rows=22 width=28) (never executed)

  • Recheck Cond: ((department_id)::text = (d.id)::text)
44. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_org_users_dept_id (cost=0.00..4.58 rows=22 width=0) (never executed)

  • Index Cond: ((department_id)::text = (d.id)::text)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_transactions_org_user_id on transactions tx_2 (cost=0.43..2,537.36 rows=1 width=5,038) (never executed)

  • Index Cond: ((org_user_id)::text = (ou_1.id)::text)
  • Filter: ((SubPlan 10) ~~ '%oukRlv4I8fk3%'::text)
46.          

SubPlan (for Index Scan)

47. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_10 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_2.report_id)::text)
49.          

SubPlan (for Nested Loop)

50. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_9 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_2.report_id)::text)
52. 338.038 6,144.460 ↓ 1.2 64,511 1

Sort (cost=1,050,891.63..1,051,031.64 rows=56,005 width=5,051) (actual time=6,083.074..6,144.460 rows=64,511 loops=1)

  • Sort Key: tx_3.created_at
  • Sort Method: external merge Disk: 97200kB
53. 128.453 5,806.422 ↓ 1.2 64,511 1

GroupAggregate (cost=801,824.58..803,364.72 rows=56,005 width=5,051) (actual time=5,501.166..5,806.422 rows=64,511 loops=1)

  • Group Key: tx_3.id
  • Filter: (string_agg((ra.approver_id)::text, ','::text) ~~ '%oukRlv4I8fk3%'::text)
54. 688.606 5,677.969 ↓ 1.2 64,511 1

Sort (cost=801,824.58..801,964.59 rows=56,005 width=5,051) (actual time=5,501.143..5,677.969 rows=64,511 loops=1)

  • Sort Key: tx_3.id
  • Sort Method: external merge Disk: 97184kB
55. 786.682 4,989.363 ↓ 1.2 64,511 1

Hash Join (cost=7,847.61..554,297.67 rows=56,005 width=5,051) (actual time=15.253..4,989.363 rows=64,511 loops=1)

  • Hash Cond: ((tx_3.report_id)::text = (ra.report_id)::text)
56. 4,187.908 4,187.908 ↑ 1.0 2,780,385 1

Seq Scan on transactions tx_3 (cost=0.00..381,384.02 rows=2,800,102 width=5,038) (actual time=0.004..4,187.908 rows=2,780,385 loops=1)

57. 2.231 14.773 ↑ 1.0 5,895 1

Hash (cost=7,773.00..7,773.00 rows=5,969 width=26) (actual time=14.773..14.773 rows=5,895 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 398kB
58. 5.094 12.542 ↑ 1.0 5,895 1

Bitmap Heap Scan on report_approvals ra (cost=190.68..7,773.00 rows=5,969 width=26) (actual time=7.756..12.542 rows=5,895 loops=1)

  • Recheck Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
  • Heap Blocks: exact=2411
59. 7.448 7.448 ↑ 1.0 5,942 1

Bitmap Index Scan on idx_report_approvals_approver_id (cost=0.00..189.19 rows=5,969 width=0) (actual time=7.448..7.448 rows=5,942 loops=1)

  • Index Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
60. 0.012 0.051 ↓ 0.0 0 1

Sort (cost=157.40..157.40 rows=1 width=5,038) (actual time=0.051..0.051 rows=0 loops=1)

  • Sort Key: tx_4.created_at
  • Sort Method: quicksort Memory: 25kB
61. 0.001 0.039 ↓ 0.0 0 1

Nested Loop (cost=19.11..157.39 rows=1 width=5,038) (actual time=0.039..0.039 rows=0 loops=1)

62. 0.001 0.038 ↓ 0.0 0 1

Nested Loop (cost=18.83..144.12 rows=1 width=5,061) (actual time=0.038..0.038 rows=0 loops=1)

63. 0.001 0.037 ↓ 0.0 0 1

Nested Loop (cost=18.40..123.20 rows=1 width=36) (actual time=0.037..0.037 rows=0 loops=1)

64. 0.010 0.036 ↓ 0.0 0 1

Hash Join (cost=18.13..122.88 rows=1 width=34) (actual time=0.036..0.036 rows=0 loops=1)

  • Hash Cond: ((hr.trip_request_id)::text = (tra.trip_request_id)::text)
65. 0.008 0.008 ↑ 1,528.0 1 1

Seq Scan on hotel_requests hr (cost=0.00..93.28 rows=1,528 width=22) (actual time=0.008..0.008 rows=1 loops=1)

66. 0.000 0.018 ↓ 0.0 0 1

Hash (cost=18.08..18.08 rows=4 width=12) (actual time=0.018..0.018 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
67. 0.002 0.018 ↓ 0.0 0 1

Bitmap Heap Scan on trip_request_approvals tra (cost=4.31..18.08 rows=4 width=12) (actual time=0.018..0.018 rows=0 loops=1)

  • Recheck Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
68. 0.016 0.016 ↓ 0.0 0 1

Bitmap Index Scan on idx_trip_request_approvals_approver_id (cost=0.00..4.31 rows=4 width=0) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
69. 0.000 0.000 ↓ 0.0 0

Index Scan using hotel_bookings_pkey on hotel_bookings hb (cost=0.27..0.32 rows=1 width=24) (never executed)

  • Index Cond: ((id)::text = (hr.id)::text)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using transactions_pkey on transactions tx_4 (cost=0.43..20.90 rows=1 width=5,038) (never executed)

  • Index Cond: ((id)::text = (hb.transaction_id)::text)
  • Filter: ((SubPlan 8) ~~ '%oukRlv4I8fk3%'::text)
71.          

SubPlan (for Index Scan)

72. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_8 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_4.report_id)::text)
74. 0.000 0.000 ↓ 0.0 0

Index Only Scan using trip_requests_pkey on trip_requests tr (cost=0.28..0.79 rows=1 width=12) (never executed)

  • Index Cond: (id = (hr.trip_request_id)::text)
  • Heap Fetches: 0
75.          

SubPlan (for Nested Loop)

76. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_7 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_4.report_id)::text)
78. 0.003 0.123 ↓ 0.0 0 1

Sort (cost=153.48..153.49 rows=1 width=5,038) (actual time=0.123..0.123 rows=0 loops=1)

  • Sort Key: tx_5.created_at
  • Sort Method: quicksort Memory: 25kB
79. 0.000 0.120 ↓ 0.0 0 1

Nested Loop (cost=20.47..153.47 rows=1 width=5,038) (actual time=0.120..0.120 rows=0 loops=1)

80. 0.001 0.120 ↓ 0.0 0 1

Nested Loop (cost=20.19..140.20 rows=1 width=5,061) (actual time=0.120..0.120 rows=0 loops=1)

81. 0.005 0.119 ↓ 0.0 0 1

Hash Join (cost=19.76..119.27 rows=1 width=36) (actual time=0.119..0.119 rows=0 loops=1)

  • Hash Cond: ((hr_1.trip_request_id)::text = (tra_1.trip_request_id)::text)
82. 0.041 0.107 ↑ 28.0 1 1

Hash Join (cost=1.63..100.92 rows=28 width=24) (actual time=0.107..0.107 rows=1 loops=1)

  • Hash Cond: ((hr_1.id)::text = (hbc.id)::text)
83. 0.035 0.035 ↑ 13.9 110 1

Seq Scan on hotel_requests hr_1 (cost=0.00..93.28 rows=1,528 width=22) (actual time=0.002..0.035 rows=110 loops=1)

84. 0.016 0.031 ↓ 1.3 36 1

Hash (cost=1.28..1.28 rows=28 width=21) (actual time=0.031..0.031 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
85. 0.015 0.015 ↓ 1.3 36 1

Seq Scan on hotel_booking_cancellations hbc (cost=0.00..1.28 rows=28 width=21) (actual time=0.005..0.015 rows=36 loops=1)

86. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=18.08..18.08 rows=4 width=12) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
87. 0.001 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on trip_request_approvals tra_1 (cost=4.31..18.08 rows=4 width=12) (actual time=0.007..0.007 rows=0 loops=1)

  • Recheck Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
88. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on idx_trip_request_approvals_approver_id (cost=0.00..4.31 rows=4 width=0) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
89. 0.000 0.000 ↓ 0.0 0

Index Scan using transactions_pkey on transactions tx_5 (cost=0.43..20.92 rows=1 width=5,038) (never executed)

  • Index Cond: ((id)::text = (hbc.transaction_id)::text)
  • Filter: ((SubPlan 6) ~~ '%oukRlv4I8fk3%'::text)
90.          

SubPlan (for Index Scan)

91. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

92. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_6 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_5.report_id)::text)
93. 0.000 0.000 ↓ 0.0 0

Index Only Scan using trip_requests_pkey on trip_requests tr_1 (cost=0.28..0.79 rows=1 width=12) (never executed)

  • Index Cond: (id = (hr_1.trip_request_id)::text)
  • Heap Fetches: 0
94.          

SubPlan (for Nested Loop)

95. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_5 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_5.report_id)::text)
97. 0.002 0.018 ↓ 0.0 0 1

Sort (cost=861.87..861.87 rows=1 width=5,038) (actual time=0.018..0.018 rows=0 loops=1)

  • Sort Key: tx_6.created_at
  • Sort Method: quicksort Memory: 25kB
98. 0.001 0.016 ↓ 0.0 0 1

Nested Loop (cost=40.08..861.86 rows=1 width=5,038) (actual time=0.016..0.016 rows=0 loops=1)

99. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=39.65..808.31 rows=2 width=13) (actual time=0.015..0.015 rows=0 loops=1)

100. 0.004 0.015 ↓ 0.0 0 1

Hash Join (cost=39.37..806.88 rows=4 width=12) (actual time=0.015..0.015 rows=0 loops=1)

  • Hash Cond: ((tpreq.trip_request_id)::text = (tr_2.id)::text)
101. 0.003 0.003 ↑ 8,980.0 1 1

Seq Scan on transportation_requests tpreq (cost=0.00..733.80 rows=8,980 width=24) (actual time=0.003..0.003 rows=1 loops=1)

102. 0.002 0.008 ↓ 0.0 0 1

Hash (cost=39.32..39.32 rows=4 width=24) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
103. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=4.60..39.32 rows=4 width=24) (actual time=0.006..0.006 rows=0 loops=1)

104. 0.001 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on trip_request_approvals tra_2 (cost=4.31..18.08 rows=4 width=12) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
105. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on idx_trip_request_approvals_approver_id (cost=0.00..4.31 rows=4 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
106. 0.000 0.000 ↓ 0.0 0

Index Only Scan using trip_requests_pkey on trip_requests tr_2 (cost=0.28..5.30 rows=1 width=12) (never executed)

  • Index Cond: (id = (tra_2.trip_request_id)::text)
  • Heap Fetches: 0
107. 0.000 0.000 ↓ 0.0 0

Index Scan using transportation_bookings_pkey on transportation_bookings tb (cost=0.28..0.35 rows=1 width=25) (never executed)

  • Index Cond: ((id)::text = (tpreq.id)::text)
108. 0.000 0.000 ↓ 0.0 0

Index Scan using transactions_pkey on transactions tx_6 (cost=0.43..20.53 rows=1 width=5,038) (never executed)

  • Index Cond: ((id)::text = (tb.transaction_id)::text)
  • Filter: ((SubPlan 4) ~~ '%oukRlv4I8fk3%'::text)
109.          

SubPlan (for Index Scan)

110. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

111. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_4 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_6.report_id)::text)
112.          

SubPlan (for Nested Loop)

113. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

114. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_3 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_6.report_id)::text)
115. 0.003 0.187 ↓ 0.0 0 1

Sort (cost=832.09..832.10 rows=1 width=5,038) (actual time=0.187..0.187 rows=0 loops=1)

  • Sort Key: tx_7.created_at
  • Sort Method: quicksort Memory: 25kB
116. 0.000 0.184 ↓ 0.0 0 1

Nested Loop (cost=27.70..832.08 rows=1 width=5,038) (actual time=0.184..0.184 rows=0 loops=1)

117. 0.001 0.184 ↓ 0.0 0 1

Nested Loop (cost=27.42..819.17 rows=1 width=5,062) (actual time=0.184..0.184 rows=0 loops=1)

118. 0.004 0.183 ↓ 0.0 0 1

Hash Join (cost=26.99..798.25 rows=1 width=37) (actual time=0.183..0.183 rows=0 loops=1)

  • Hash Cond: ((tpreq_1.trip_request_id)::text = (tra_3.trip_request_id)::text)
119. 0.018 0.172 ↑ 216.0 1 1

Hash Join (cost=8.86..778.50 rows=216 width=25) (actual time=0.172..0.172 rows=1 loops=1)

  • Hash Cond: ((tpreq_1.id)::text = (tbc.id)::text)
120. 0.013 0.013 ↑ 213.8 42 1

Seq Scan on transportation_requests tpreq_1 (cost=0.00..733.80 rows=8,980 width=24) (actual time=0.001..0.013 rows=42 loops=1)

121. 0.070 0.141 ↓ 1.0 218 1

Hash (cost=6.16..6.16 rows=216 width=25) (actual time=0.141..0.141 rows=218 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
122. 0.071 0.071 ↓ 1.0 218 1

Seq Scan on transportation_booking_cancellations tbc (cost=0.00..6.16 rows=216 width=25) (actual time=0.005..0.071 rows=218 loops=1)

123. 0.000 0.007 ↓ 0.0 0 1

Hash (cost=18.08..18.08 rows=4 width=12) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
124. 0.002 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on trip_request_approvals tra_3 (cost=4.31..18.08 rows=4 width=12) (actual time=0.007..0.007 rows=0 loops=1)

  • Recheck Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
125. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on idx_trip_request_approvals_approver_id (cost=0.00..4.31 rows=4 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: ((approver_id)::text = 'oukRlv4I8fk3'::text)
126. 0.000 0.000 ↓ 0.0 0

Index Scan using transactions_pkey on transactions tx_7 (cost=0.43..20.91 rows=1 width=5,038) (never executed)

  • Index Cond: ((id)::text = (tbc.transaction_id)::text)
  • Filter: ((SubPlan 2) ~~ '%oukRlv4I8fk3%'::text)
127.          

SubPlan (for Index Scan)

128. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

129. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_2 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_7.report_id)::text)
130. 0.000 0.000 ↓ 0.0 0

Index Only Scan using trip_requests_pkey on trip_requests tr_3 (cost=0.28..0.43 rows=1 width=12) (never executed)

  • Index Cond: (id = (tpreq_1.trip_request_id)::text)
  • Heap Fetches: 0
131.          

SubPlan (for Nested Loop)

132. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=12.46..12.47 rows=1 width=13) (never executed)

133. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_report_approvals_report_id on report_approvals ra_1 (cost=0.42..12.46 rows=2 width=13) (never executed)

  • Index Cond: ((report_id)::text = (tx_7.report_id)::text)
134. 516.088 516.088 ↑ 1.0 1 64,511

Index Scan using org_users_pkey on org_users ou (cost=0.42..1.17 rows=1 width=132) (actual time=0.008..0.008 rows=1 loops=64,511)

  • Index Cond: ((id)::text = (tx.org_user_id)::text)
135. 451.577 451.577 ↑ 1.0 1 64,511

Index Scan using users_pkey on users u (cost=0.42..0.85 rows=1 width=59) (actual time=0.007..0.007 rows=1 loops=64,511)

  • Index Cond: ((id)::text = (ou.user_id)::text)
136. 14.182 27.643 ↓ 1.0 38,610 1

Hash (cost=1,874.82..1,874.82 rows=38,282 width=37) (actual time=27.643..27.643 rows=38,610 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3143kB
137. 13.461 13.461 ↓ 1.0 38,610 1

Seq Scan on orgs o (cost=0.00..1,874.82 rows=38,282 width=37) (actual time=0.004..13.461 rows=38,610 loops=1)

138. 193.533 193.533 ↑ 1.0 1 64,511

Index Scan using org_categories_pkey on org_categories oc (cost=0.42..1.80 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=64,511)

  • Index Cond: (id = tx.org_category_id)
139. 580.599 580.599 ↑ 1.0 1 64,511

Index Scan using accounts_pkey on accounts source_account (cost=0.42..0.98 rows=1 width=37) (actual time=0.008..0.009 rows=1 loops=64,511)

  • Index Cond: ((id)::text = (tx.source_account_id)::text)
140. 129.022 129.022 ↓ 0.0 0 64,511

Index Scan using vendors_pkey on vendors v (cost=0.42..1.04 rows=1 width=22) (actual time=0.001..0.002 rows=0 loops=64,511)

  • Index Cond: (tx.vendor_id = id)
141. 8.297 20.068 ↓ 1.0 21,670 1

Hash (cost=3,718.52..3,718.52 rows=21,652 width=46) (actual time=20.068..20.068 rows=21,670 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1916kB
142. 11.771 11.771 ↓ 1.0 21,670 1

Seq Scan on projects pj (cost=0.00..3,718.52 rows=21,652 width=46) (actual time=0.005..11.771 rows=21,670 loops=1)

143. 7.443 15.079 ↑ 1.0 18,886 1

Hash (cost=986.31..986.31 rows=18,931 width=34) (actual time=15.079..15.079 rows=18,886 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1510kB
144. 7.636 7.636 ↑ 1.0 18,886 1

Seq Scan on cost_centers cc (cost=0.00..986.31 rows=18,931 width=34) (actual time=0.003..7.636 rows=18,886 loops=1)

145. 8.811 17.340 ↑ 1.0 26,655 1

Hash (cost=1,059.65..1,059.65 rows=27,065 width=28) (actual time=17.340..17.340 rows=26,655 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1818kB
146. 8.529 8.529 ↑ 1.0 26,655 1

Seq Scan on ccce_transaction_mappings ccce_txn_mapping (cost=0.00..1,059.65 rows=27,065 width=28) (actual time=0.005..8.529 rows=26,655 loops=1)

147. 580.599 580.599 ↑ 1.0 1 64,511

Index Scan using reports_pkey on reports r (cost=0.42..2.75 rows=1 width=74) (actual time=0.009..0.009 rows=1 loops=64,511)

  • Index Cond: ((tx.report_id)::text = (id)::text)
148. 322.555 322.555 ↑ 1.0 1 64,511

Index Scan using levels_pkey on levels lvl (cost=0.28..0.30 rows=1 width=41) (actual time=0.005..0.005 rows=1 loops=64,511)

  • Index Cond: ((ou.level_id)::text = (id)::text)
  • Filter: ((ou.org_id)::text = (org_id)::text)
149. 451.577 451.577 ↑ 1.0 1 64,511

Index Scan using departments_pkey on departments dept (cost=0.29..0.32 rows=1 width=56) (actual time=0.006..0.007 rows=1 loops=64,511)

  • Index Cond: ((ou.department_id)::text = (id)::text)
  • Filter: ((ou.org_id)::text = (org_id)::text)
150. 258.044 258.044 ↓ 0.0 0 64,511

Index Scan using idx_transaction_external_expense_mappings_transaction_id on transaction_external_expense_mappings external_expense (cost=0.28..0.29 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=64,511)

  • Index Cond: ((tx.id)::text = (transaction_id)::text)
Planning time : 19.804 ms
Execution time : 12,238.299 ms