explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oCQV : bad

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 63,911.100 ↓ 10.0 10 1

Limit (cost=656,421.43..656,421.43 rows=1 width=9,617) (actual time=63,911.093..63,911.1 rows=10 loops=1)

  • Buffers: shared hit=6634071, temp read=13657 written=13657
2. 10.840 63,911.055 ↓ 210.0 210 1

Sort (cost=656,421.43..656,421.43 rows=1 width=9,617) (actual time=63,910.996..63,911.055 rows=210 loops=1)

  • Sort Key: tx.created_at
  • Sort Method: top-N heapsort Memory: 448kB
  • Buffers: shared hit=6634071, temp read=13657 written=13657
3. 6,431.667 63,900.215 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.14..656,421.42 rows=1 width=9,617) (actual time=1,177.828..63,900.215 rows=1,035 loops=1)

  • Buffers: shared hit=6634068, temp read=13657 written=13657
4. 236.814 51,757.418 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.14..655,353.83 rows=1 width=9,602) (actual time=1,165.449..51,757.418 rows=1,035 loops=1)

  • Buffers: shared hit=6079308, temp read=13657 written=13657
5. 4,831.944 51,311.534 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.14..655,317.66 rows=1 width=9,582) (actual time=1,164.993..51,311.534 rows=1,035 loops=1)

  • Buffers: shared hit=6061713, temp read=13657 written=13657
6. 2,558.179 41,054.120 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.14..654,055.36 rows=1 width=9,565) (actual time=1,163.065..41,054.12 rows=1,035 loops=1)

  • Buffers: shared hit=5241993, temp read=13657 written=13657
7. 550.909 36,299.671 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.14..653,543.91 rows=1 width=9,537) (actual time=1,158.702..36,299.671 rows=1,035 loops=1)

  • Buffers: shared hit=4875603, temp read=13657 written=13657
8. 5.655 35,190.897 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,099.14..653,354.39 rows=1 width=9,537) (actual time=1,158.113..35,190.897 rows=1,035 loops=1)

  • Buffers: shared hit=4726563, temp read=13657 written=13657
9. 5,366.137 35,166.612 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,098.72..653,351.19 rows=1 width=9,477) (actual time=1,158.085..35,166.612 rows=1,035 loops=1)

  • Buffers: shared hit=4722430, temp read=13657 written=13657
10. 5.163 23,764.355 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,098.72..650,086.36 rows=1 width=9,434) (actual time=1,144.428..23,764.355 rows=1,035 loops=1)

  • Buffers: shared hit=1737490, temp read=13657 written=13657
11. 5.377 23,742.632 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,098.3..650,085.24 rows=1 width=9,397) (actual time=1,144.398..23,742.632 rows=1,035 loops=1)

  • Buffers: shared hit=1733349, temp read=13657 written=13657
12. 11,912.937 23,727.940 ↓ 1,035.0 1,035 1

Nested Loop (cost=621,097.88..650,082.81 rows=1 width=9,365) (actual time=1,144.379..23,727.94 rows=1,035 loops=1)

  • Buffers: shared hit=1729209, temp read=13657 written=13657
13. 4.511 1,465.003 ↓ 1,035.0 1,035 1

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

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

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

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

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

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

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

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

Sort (cost=621,097.04..621,193.52 rows=38,592 width=5,049) (actual time=1,062.054..1,189.7 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.449 839.976 ↓ 1.0 38,887 1

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

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

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

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

Bitmap Heap Scan on transactions tx (cost=9.85..731.18 rows=183 width=5,049) (actual time=0.084..1.171 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.001 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.038 ↓ 0.0 0 1

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

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

Bitmap Index Scan on idx_projects_approver1_id (cost=0..4.32 rows=5 width=0) (actual time=0.014..0.014 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.006 2.645 ↓ 0.0 0 1

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

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

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

  • Buffers: shared hit=354
31. 0.001 2.639 ↓ 0.0 0 1

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

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

Seq Scan on departments d (cost=0..432.73 rows=1 width=15) (actual time=2.638..2.638 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. 200.569 818.039 ↓ 1.0 38,746 1

Sort (cost=427,610.51..427,705.14 rows=37,852 width=5,049) (actual time=783.313..818.039 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. 48.892 617.470 ↓ 1.0 38,746 1

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

  • Buffers: shared hit=169911
38. 59.688 64.880 ↓ 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.504..64.88 rows=38,746 loops=1)

  • Heap Blocks: exact=14305
  • Buffers: shared hit=14498
39. 5.192 5.192 ↓ 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.192..5.192 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.003 1.294 ↓ 0.0 0 1

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

  • Buffers: shared hit=97
42. 0.000 1.291 ↓ 0.0 0 1

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

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

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

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

Hash Join (cost=177.93..269.42 rows=1 width=32) (actual time=1.29..1.29 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.002 1.229 ↓ 0.0 0 1

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

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

Seq Scan on trip_request_approvals tra (cost=0..177.88 rows=4 width=12) (actual time=1.227..1.227 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. 17.939 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.835 59.835 ↑ 1.0 2 1

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

  • Filter: ((v.display_name)::text = 'uber'::text)
  • Buffers: shared hit=6048
53. 18.810 18.810 ↑ 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.017..0.018 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. 13.455 13.455 ↑ 1.0 1 1,035

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

  • Index Cond: ((u.id)::text = (ou.user_id)::text)
  • Buffers: shared hit=4142
55. 10,350.000 10,350.000 ↓ 1.0 38,353 1,035

Seq Scan on orgs o (cost=0..1,874.82 rows=38,282 width=37) (actual time=0.003..10 rows=38,353 loops=1,035)

  • Buffers: shared hit=1544220
56. 9.315 9.315 ↑ 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.008..0.009 rows=1 loops=1,035)

  • Index Cond: (oc.id = tx.org_category_id)
  • Buffers: shared hit=4140
57. 16.560 16.560 ↑ 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.015..0.016 rows=1 loops=1,035)

  • Index Cond: ((source_account.id)::text = (tx.source_account_id)::text)
  • Buffers: shared hit=4141
58. 6,036.120 6,036.120 ↑ 1.0 16,925 1,035

Seq Scan on projects pj (cost=0..3,053.26 rows=16,926 width=47) (actual time=0.003..5.832 rows=16,925 loops=1,035)

  • Buffers: shared hit=2984940
59. 18.630 18.630 ↑ 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.016..0.018 rows=1 loops=1,035)

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

Seq Scan on levels lvl (cost=0..162.21 rows=1,821 width=41) (actual time=0.004..0.539 rows=1,821 loops=1,035)

  • Buffers: shared hit=149040
61. 2,196.270 2,196.270 ↓ 1.3 8,443 1,035

Seq Scan on departments dept (cost=0..416.98 rows=6,298 width=55) (actual time=0.001..2.122 rows=8,443 loops=1,035)

  • Buffers: shared hit=366390
62. 5,425.470 5,425.470 ↑ 1.0 18,786 1,035

Seq Scan on cost_centers cc (cost=0..980.12 rows=18,812 width=34) (actual time=0.001..5.242 rows=18,786 loops=1,035)

  • Buffers: shared hit=819720
63. 209.070 209.070 ↑ 1.0 817 1,035

Seq Scan on transaction_external_expense_mappings external_expense (cost=0..25.52 rows=852 width=33) (actual time=0.004..0.202 rows=817 loops=1,035)

  • Buffers: shared hit=17595
64. 5,711.130 5,711.130 ↑ 1.1 22,012 1,035

Seq Scan on ccce_transaction_mappings ccce_txn_mapping (cost=0..772.26 rows=23,626 width=28) (actual time=0.003..5.518 rows=22,012 loops=1,035)

  • Buffers: shared hit=554760
Planning time : 19.845 ms
Execution time : 63,928.995 ms