explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PyTX : good

Settings
# exclusive inclusive rows x rows loops node
1. 0.336 1,468.425 ↓ 835.0 835 1

Limit (cost=647,738.98..647,738.98 rows=1 width=9,617) (actual time=1,467.888..1,468.425 rows=835 loops=1)

  • Buffers: shared hit=213962, temp read=13657 written=13657
2. 4.642 1,468.089 ↓ 1,035.0 1,035 1

Sort (cost=647,738.98..647,738.98 rows=1 width=9,617) (actual time=1,467.79..1,468.089 rows=1,035 loops=1)

  • Sort Key: tx.created_at
  • Sort Method: quicksort Memory: 1721kB
  • Buffers: shared hit=213962, temp read=13657 written=13657
3. 1.286 1,463.447 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,101.13..647,738.97 rows=1 width=9,617) (actual time=1,111.876..1,463.447 rows=1,035 loops=1)

  • Buffers: shared hit=213959, temp read=13657 written=13657
4. 1.635 1,455.951 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,100.84..647,738.58 rows=1 width=9,602) (actual time=1,111.861..1,455.951 rows=1,035 loops=1)

  • Buffers: shared hit=211886, temp read=13657 written=13657
5. 1.929 1,450.176 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,100.57..647,738.28 rows=1 width=9,582) (actual time=1,111.846..1,450.176 rows=1,035 loops=1)

  • Buffers: shared hit=209813, temp read=13657 written=13657
6. 1.865 1,445.142 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,100.28..647,737.87 rows=1 width=9,565) (actual time=1,111.824..1,445.142 rows=1,035 loops=1)

  • Buffers: shared hit=206720, temp read=13657 written=13657
7. 1.724 1,436.032 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,100..647,737.55 rows=1 width=9,537) (actual time=1,111.796..1,436.032 rows=1,035 loops=1)

  • Buffers: shared hit=203615, temp read=13657 written=13657
8. 1.881 1,428.098 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.72..647,737.23 rows=1 width=9,537) (actual time=1,111.773..1,428.098 rows=1,035 loops=1)

  • Buffers: shared hit=200508, temp read=13657 written=13657
9. 1.692 1,414.832 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.3..647,734.04 rows=1 width=9,477) (actual time=1,111.747..1,414.832 rows=1,035 loops=1)

  • Buffers: shared hit=196375, temp read=13657 written=13657
10. 1.806 1,413.140 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.01..647,733.39 rows=1 width=9,434) (actual time=1,111.743..1,413.14 rows=1,035 loops=1)

  • Buffers: shared hit=196375, temp read=13657 written=13657
11. 1.551 1,400.984 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,098.59..647,732.27 rows=1 width=9,397) (actual time=1,111.718..1,400.984 rows=1,035 loops=1)

  • Buffers: shared hit=192234, temp read=13657 written=13657
12. 1.954 1,396.328 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,098.17..647,729.85 rows=1 width=9,365) (actual time=1,111.702..1,396.328 rows=1,035 loops=1)

  • Buffers: shared hit=188094, temp read=13657 written=13657
13. 2.205 1,388.164 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,097.88..647,729.47 rows=1 width=9,341) (actual time=1,111.684..1,388.164 rows=1,035 loops=1)

  • Buffers: shared hit=184989, temp read=13657 written=13657
14. 1.485 1,377.679 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,097.46..647,728.45 rows=1 width=9,295) (actual time=1,111.658..1,377.679 rows=1,035 loops=1)

  • Buffers: shared hit=180847, temp read=13657 written=13657
15. 48.012 1,365.744 ↓ 1,045.0 1,045 1

Nested Loop (cost=621,097.04..647,726.93 rows=1 width=9,165) (actual time=1,111.619..1,365.744 rows=1,045 loops=1)

  • Buffers: shared hit=176659, temp read=13657 written=13657
16. 86.544 1,239.958 ↓ 1.0 38,887 1

Unique (cost=621,097.04..635,472.56 rows=38,592 width=5,049) (actual time=1,051.928..1,239.958 rows=38,887 loops=1)

  • Buffers: shared hit=170611, temp read=13657 written=13657
17. 322.315 1,153.414 ↓ 1.0 38,887 1

Sort (cost=621,097.04..621,193.52 rows=38,592 width=5,049) (actual time=1,051.925..1,153.414 rows=38,887 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
  • Sort Method: external merge Disk: 54584kB
  • Buffers: shared hit=170489, temp read=13657 written=13657
18. 16.520 831.099 ↓ 1.0 38,887 1

Append (cost=738.06..450,633.1 rows=38,592 width=5,049) (actual time=1.406..831.099 rows=38,887 loops=1)

  • Buffers: shared hit=170460, temp read=6824 written=6824
19. 0.319 1.433 ↑ 1.3 141 1

Sort (cost=738.06..738.52 rows=183 width=5,049) (actual time=1.406..1.433 rows=141 loops=1)

  • Sort Key: tx.created_at
  • Sort Method: quicksort Memory: 197kB
  • Buffers: shared hit=94
20. 1.062 1.114 ↑ 1.3 141 1

Bitmap Heap Scan on transactions tx (cost=9.85..731.18 rows=183 width=5,049) (actual time=0.081..1.114 rows=141 loops=1)

  • Heap Blocks: exact=90
  • Buffers: shared hit=94
21. 0.052 0.052 ↑ 1.3 141 1

Bitmap Index Scan on idx_transactions_org_user_id (cost=0..9.8 rows=183 width=0) (actual time=0.052..0.052 rows=141 loops=1)

  • Index Cond: ((tx.org_user_id)::text = 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=4
22. 0.008 0.047 ↓ 0.0 0 1

Sort (cost=20,788.26..20,788.99 rows=291 width=5,049) (actual time=0.047..0.047 rows=0 loops=1)

  • Sort Key: tx_1.created_at
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4
23. 0.000 0.039 ↓ 0.0 0 1

Nested Loop (cost=9.09..20,776.35 rows=291 width=5,049) (actual time=0.039..0.039 rows=0 loops=1)

  • Buffers: shared hit=4
24. 0.002 0.039 ↓ 0.0 0 1

Bitmap Heap Scan on projects p (cost=8.66..50.78 rows=11 width=4) (actual time=0.039..0.039 rows=0 loops=1)

  • Buffers: shared hit=4
25. 0.001 0.037 ↓ 0.0 0 1

BitmapOr (cost=8.66..8.66 rows=11 width=0) (actual time=0.037..0.037 rows=0 loops=1)

  • Buffers: shared hit=4
26. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on idx_projects_approver1_id (cost=0..4.32 rows=5 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((p.approver1_id)::text = 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=2
27. 0.023 0.023 ↓ 0.0 0 1

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

  • Index Cond: ((p.approver2_id)::text = 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=2
28. 0.000 0.000 ↓ 0.0 0 0

Index Scan using idx_transactions_project_id on transactions tx_1 (cost=0.43..1,878.29 rows=585 width=5,049) (never executed)

  • Index Cond: (tx_1.project_id = p.id)
29. 0.003 2.586 ↓ 0.0 0 1

Sort (cost=734.71..735.37 rows=265 width=5,049) (actual time=2.586..2.586 rows=0 loops=1)

  • Sort Key: tx_2.created_at
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=354
30. 0.002 2.583 ↓ 0.0 0 1

Nested Loop (cost=5.01..724.05 rows=265 width=5,049) (actual time=2.583..2.583 rows=0 loops=1)

  • Buffers: shared hit=354
31. 0.000 2.581 ↓ 0.0 0 1

Nested Loop (cost=4.58..518.8 rows=11 width=13) (actual time=2.581..2.581 rows=0 loops=1)

  • Buffers: shared hit=354
32. 2.581 2.581 ↓ 0.0 0 1

Seq Scan on departments d (cost=0..432.73 rows=1 width=15) (actual time=2.581..2.581 rows=0 loops=1)

  • Filter: ((d.department_head_id)::text = 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=354
33. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on org_users ou_1 (cost=4.58..85.87 rows=21 width=28) (never executed)

34. 0.000 0.000 ↓ 0.0 0 0

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

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

Index Scan using idx_transactions_org_user_id on transactions tx_2 (cost=0.43..16.69 rows=197 width=5,049) (never executed)

  • Index Cond: ((tx_2.org_user_id)::text = (ou_1.id)::text)
36. 197.119 809.243 ↓ 1.0 38,746 1

Sort (cost=427,610.51..427,705.14 rows=37,852 width=5,049) (actual time=774.794..809.243 rows=38,746 loops=1)

  • Sort Key: tx_3.created_at
  • Sort Method: external merge Disk: 54536kB
  • Buffers: shared hit=169911, temp read=6824 written=6824
37. 44.807 612.124 ↓ 1.0 38,746 1

Nested Loop (cost=1,262.21..260,421.23 rows=37,852 width=5,049) (actual time=7.581..612.124 rows=38,746 loops=1)

  • Buffers: shared hit=169911
38. 58.395 63.619 ↓ 1.0 38,746 1

Bitmap Heap Scan on transaction_approvals ta (cost=1,261.78..49,963.94 rows=37,852 width=13) (actual time=7.528..63.619 rows=38,746 loops=1)

  • Heap Blocks: exact=14305
  • Buffers: shared hit=14498
39. 5.224 5.224 ↓ 1.0 38,746 1

Bitmap Index Scan on idx_transaction_approvals_approver_id (cost=0..1,252.32 rows=37,852 width=0) (actual time=5.224..5.224 rows=38,746 loops=1)

  • Index Cond: ((ta.approver_id)::text = 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=193
40. 503.698 503.698 ↑ 1.0 1 38,746

Index Scan using transactions_pkey on transactions tx_3 (cost=0.43..5.55 rows=1 width=5,049) (actual time=0.012..0.013 rows=1 loops=38,746)

  • Index Cond: ((tx_3.id)::text = (ta.transaction_id)::text)
  • Buffers: shared hit=155413
41. 0.001 1.270 ↓ 0.0 0 1

Nested Loop (cost=178.78..279.16 rows=1 width=5,049) (actual time=1.27..1.27 rows=0 loops=1)

  • Buffers: shared hit=97
42. 0.001 1.269 ↓ 0.0 0 1

Nested Loop (cost=178.5..278.05 rows=1 width=5,071) (actual time=1.269..1.269 rows=0 loops=1)

  • Buffers: shared hit=97
43. 0.001 1.268 ↓ 0.0 0 1

Nested Loop (cost=178.07..269.62 rows=1 width=35) (actual time=1.268..1.268 rows=0 loops=1)

  • Buffers: shared hit=97
44. 0.052 1.267 ↓ 0.0 0 1

Hash Join (cost=177.93..269.42 rows=1 width=32) (actual time=1.267..1.267 rows=0 loops=1)

  • Buffers: shared hit=97
45. 0.009 0.009 ↑ 1,285.0 1 1

Seq Scan on hotel_requests hr (cost=0..81.85 rows=1,285 width=20) (actual time=0.009..0.009 rows=1 loops=1)

  • Buffers: shared hit=1
46. 0.001 1.206 ↓ 0.0 0 1

Hash (cost=177.88..177.88 rows=4 width=12) (actual time=1.206..1.206 rows=0 loops=1)

  • Buffers: shared hit=93
47. 1.205 1.205 ↓ 0.0 0 1

Seq Scan on trip_request_approvals tra (cost=0..177.88 rows=4 width=12) (actual time=1.205..1.205 rows=0 loops=1)

  • Filter: ((tra.approver_id)::text = 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=93
48. 0.000 0.000 ↓ 0.0 0 0

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

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

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

  • Index Cond: ((tx_4.id)::text = (hb.transaction_id)::text)
50. 0.000 0.000 ↓ 0.0 0 0

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

  • Index Cond: (tr.id = (hr.trip_request_id)::text)
51. 18.245 77.774 ↑ 1.0 2 38,887

Materialize (cost=0..10,710.7 rows=2 width=22) (actual time=0.001..0.002 rows=2 loops=38,887)

  • Buffers: shared hit=6048
52. 59.529 59.529 ↑ 1.0 2 1

Seq Scan on vendors v (cost=0..10,710.69 rows=2 width=22) (actual time=19.777..59.529 rows=2 loops=1)

  • Filter: ((v.display_name)::text = 'uber'::text)
  • Buffers: shared hit=6048
53. 10.450 10.450 ↑ 1.0 1 1,045

Index Scan using org_users_pkey on org_users ou (cost=0.42..1.51 rows=1 width=130) (actual time=0.009..0.01 rows=1 loops=1,045)

  • Index Cond: ((ou.id)::text = (tx.org_user_id)::text)
  • Filter: ((ou.id)::text <> 'ouaLzP7PPUPC'::text)
  • Buffers: shared hit=4188
54. 8.280 8.280 ↑ 1.0 1 1,035

Index Scan using users_pkey on users u (cost=0.42..1 rows=1 width=59) (actual time=0.008..0.008 rows=1 loops=1,035)

  • Index Cond: ((u.id)::text = (ou.user_id)::text)
  • Buffers: shared hit=4142
55. 6.210 6.210 ↑ 1.0 1 1,035

Index Scan using orgs_pkey on orgs o (cost=0.29..0.37 rows=1 width=37) (actual time=0.006..0.006 rows=1 loops=1,035)

  • Index Cond: ((o.id)::text = (ou.org_id)::text)
  • Buffers: shared hit=3105
56. 3.105 3.105 ↑ 1.0 1 1,035

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

  • Index Cond: (oc.id = tx.org_category_id)
  • Buffers: shared hit=4140
57. 10.350 10.350 ↑ 1.0 1 1,035

Index Scan using accounts_pkey on accounts source_account (cost=0.42..1.11 rows=1 width=37) (actual time=0.009..0.01 rows=1 loops=1,035)

  • Index Cond: ((source_account.id)::text = (tx.source_account_id)::text)
  • Buffers: shared hit=4141
58. 0.000 0.000 ↓ 0.0 0 1,035

Index Scan using projects_pkey on projects pj (cost=0.29..0.64 rows=1 width=47) (actual time=0..0 rows=0 loops=1,035)

  • Index Cond: (tx.project_id = pj.id)
59. 11.385 11.385 ↑ 1.0 1 1,035

Index Scan using reports_pkey on reports r (cost=0.42..3.18 rows=1 width=73) (actual time=0.01..0.011 rows=1 loops=1,035)

  • Index Cond: ((tx.report_id)::text = (r.id)::text)
  • Buffers: shared hit=4133
60. 6.210 6.210 ↑ 1.0 1 1,035

Index Scan using levels_pkey on levels lvl (cost=0.28..0.3 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=1,035)

  • Index Cond: ((ou.level_id)::text = (lvl.id)::text)
  • Filter: ((ou.org_id)::text = (lvl.org_id)::text)
  • Buffers: shared hit=3107
61. 7.245 7.245 ↑ 1.0 1 1,035

Index Scan using departments_pkey on departments dept (cost=0.28..0.32 rows=1 width=55) (actual time=0.007..0.007 rows=1 loops=1,035)

  • Index Cond: ((ou.department_id)::text = (dept.id)::text)
  • Filter: ((ou.org_id)::text = (dept.org_id)::text)
  • Buffers: shared hit=3105
62. 3.105 3.105 ↑ 1.0 1 1,035

Index Scan using cost_centers_pkey on cost_centers cc (cost=0.29..0.39 rows=1 width=34) (actual time=0.002..0.003 rows=1 loops=1,035)

  • Index Cond: (tx.cost_center_id = cc.id)
  • Buffers: shared hit=3093
63. 4.140 4.140 ↓ 0.0 0 1,035

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=1,035)

  • Index Cond: ((tx.id)::text = (external_expense.transaction_id)::text)
  • Buffers: shared hit=2073
64. 6.210 6.210 ↓ 0.0 0 1,035

Index Scan using unique_transaction_id on ccce_transaction_mappings ccce_txn_mapping (cost=0.29..0.37 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=1,035)

  • Index Cond: ((tx.id)::text = (ccce_txn_mapping.transaction_id)::text)
  • Buffers: shared hit=2073
Planning time : 19.882 ms
Execution time : 1,487.96 ms