explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Yfw

Settings
# exclusive inclusive rows x rows loops node
1. 127.259 7,282.477 ↑ 3.1 107,011 1

GroupAggregate (cost=521,933.77..541,917.79 rows=333,067 width=332) (actual time=7,123.386..7,282.477 rows=107,011 loops=1)

  • Group Key: invoices.id, insurers.name, order_items.purchasable_name, order_items.purchasable_type, order_items.staff_member_id, orders.location_id, orders.patient_id, (concat(patient_users.first_name, ' ', patient_users.last_name)), (string_agg(invoice_items.description, '
  • Buffers: shared hit=11800 read=45026, temp read=45251 written=45247
2. 226.653 7,155.218 ↑ 2.8 118,442 1

Sort (cost=521,933.77..522,766.43 rows=333,067 width=280) (actual time=7,123.358..7,155.218 rows=118,442 loops=1)

  • Sort Key: invoices.id, insurers.name, order_items.purchasable_name, order_items.purchasable_type, order_items.staff_member_id, orders.location_id, orders.patient_id, (concat(patient_users.first_name, ' ', patient_users.last_name)), (string_agg(invoice_items.description, '
  • Sort Method: external merge Disk: 46968kB
  • Buffers: shared hit=11800 read=45026, temp read=45251 written=45247
3. 122.196 6,928.565 ↑ 2.8 118,442 1

Merge Left Join (cost=441,926.73..448,122.44 rows=333,067 width=280) (actual time=6,742.802..6,928.565 rows=118,442 loops=1)

  • Merge Cond: (orders.patient_id = patient_users.patient_id)
  • Buffers: shared hit=11797 read=45026, temp read=39380 written=39373
4. 278.987 6,639.597 ↓ 1.6 118,442 1

Sort (cost=427,780.74..427,964.26 rows=73,408 width=248) (actual time=6,597.316..6,639.597 rows=118,442 loops=1)

  • Sort Key: orders.patient_id
  • Sort Method: external merge Disk: 44928kB
  • Buffers: shared hit=11797 read=40750, temp read=39380 written=39373
5. 47.027 6,360.610 ↓ 1.6 118,442 1

Hash Left Join (cost=360,467.10..413,315.04 rows=73,408 width=248) (actual time=4,975.838..6,360.610 rows=118,442 loops=1)

  • Hash Cond: (invoices.payer_id = insurers.id)
  • Join Filter: ((invoices.payer_type)::text = 'Billing::Insurer'::text)
  • Buffers: shared hit=11797 read=40750, temp read=33764 written=33754
6. 386.855 6,312.394 ↓ 1.6 118,442 1

Hash Right Join (cost=360,458.02..413,113.22 rows=73,408 width=226) (actual time=4,974.635..6,312.394 rows=118,442 loops=1)

  • Hash Cond: (invoices_1.id = invoices.id)
  • Buffers: shared hit=11796 read=40745, temp read=33764 written=33754
7. 403.813 4,036.634 ↑ 1.0 632,124 1

GroupAggregate (cost=284,237.01..327,466.43 rows=632,124 width=68) (actual time=3,084.306..4,036.634 rows=632,124 loops=1)

  • Group Key: invoices_1.id
  • Buffers: shared hit=3 read=9057, temp read=18471 written=18473
8. 291.758 3,632.821 ↓ 1.2 839,365 1

Merge Join (cost=284,237.01..312,738.54 rows=699,470 width=128) (actual time=3,084.285..3,632.821 rows=839,365 loops=1)

  • Merge Cond: (invoice_items.invoice_id = invoices_1.id)
  • Buffers: shared hit=3 read=9057, temp read=18471 written=18473
9. 946.597 3,251.610 ↓ 1.2 839,365 1

Sort (cost=284,236.24..285,984.92 rows=699,470 width=128) (actual time=3,083.853..3,251.610 rows=839,365 loops=1)

  • Sort Key: invoice_items.invoice_id
  • Sort Method: external merge Disk: 108368kB
  • Buffers: shared read=7329, temp read=18471 written=18473
10. 126.944 2,305.013 ↓ 1.2 839,365 1

Subquery Scan on invoice_items (cost=82,300.31..170,905.54 rows=699,470 width=128) (actual time=1,710.198..2,305.013 rows=839,365 loops=1)

  • Buffers: shared read=7329, temp read=4925 written=4926
11. 0.000 2,178.069 ↓ 1.2 839,365 1

Gather Merge (cost=82,300.31..163,910.84 rows=699,470 width=136) (actual time=1,710.197..2,178.069 rows=839,365 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=58 read=21691, temp read=14580 written=14583
12. 1,266.705 5,330.784 ↑ 1.2 279,788 3

Sort (cost=81,300.28..82,174.62 rows=349,735 width=136) (actual time=1,705.673..1,776.928 rows=279,788 loops=3)

  • Sort Key: ii.created_at
  • Sort Method: external merge Disk: 39400kB
  • Buffers: shared hit=58 read=21691, temp read=14580 written=14583
13. 4,064.079 4,064.079 ↑ 1.2 279,788 3

Parallel Seq Scan on billing_invoice_items ii (cost=0.00..25,188.35 rows=349,735 width=136) (actual time=0.134..1,354.693 rows=279,788 loops=3)

  • Buffers: shared read=21691
14. 89.453 89.453 ↑ 1.0 632,124 1

Index Only Scan using billing_abstract_invoices_pkey on billing_abstract_invoices invoices_1 (cost=0.42..16,430.28 rows=632,124 width=4) (actual time=0.421..89.453 rows=632,124 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=3 read=1728
15. 54.162 1,888.905 ↓ 1.6 118,442 1

Hash (cost=75,303.40..75,303.40 rows=73,408 width=162) (actual time=1,888.905..1,888.905 rows=118,442 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 2 (originally 1) Memory Usage: 15361kB
  • Buffers: shared hit=11793 read=31688, temp read=7064 written=8380
16. 62.830 1,834.743 ↓ 1.6 118,442 1

Hash Right Join (cost=66,234.50..75,303.40 rows=73,408 width=162) (actual time=1,494.774..1,834.743 rows=118,442 loops=1)

  • Hash Cond: (ci.parent_invoice_id = invoices.id)
  • Buffers: shared hit=11793 read=31688, temp read=7064 written=7054
17. 279.671 279.671 ↓ 8.2 16,969 1

Index Scan using active_invoices on billing_abstract_invoices ci (cost=0.42..9,061.45 rows=2,081 width=16) (actual time=1.567..279.671 rows=16,969 loops=1)

  • Index Cond: ((archived_at IS NULL) AND (voided_at IS NULL) AND (parent_invoice_id IS NOT NULL))
  • Filter: (((type)::text = ANY ('{Billing::Invoice,Billing::ThirdPartyInvoice}'::text[])) AND ((payer_type)::text = 'Billing::Insurer'::text))
  • Buffers: shared hit=7560 read=2586
18. 68.382 1,492.242 ↓ 1.5 107,011 1

Hash (cost=65,316.48..65,316.48 rows=73,408 width=150) (actual time=1,492.242..1,492.242 rows=107,011 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 2 (originally 1) Memory Usage: 15361kB
  • Buffers: shared hit=4233 read=29102, temp read=5648 written=6821
19. 167.806 1,423.860 ↓ 1.5 107,011 1

Hash Right Join (cost=56,052.68..65,316.48 rows=73,408 width=150) (actual time=1,202.577..1,423.860 rows=107,011 loops=1)

  • Hash Cond: (orders.id = order_items.order_id)
  • Buffers: shared hit=4233 read=29102, temp read=5648 written=5640
20. 55.022 55.022 ↑ 1.0 326,758 1

Seq Scan on billing_orders orders (cost=0.00..5,670.58 rows=326,758 width=12) (actual time=0.514..55.022 rows=326,758 loops=1)

  • Buffers: shared read=2403
21. 65.485 1,201.032 ↓ 1.5 107,011 1

Hash (cost=55,135.08..55,135.08 rows=73,408 width=146) (actual time=1,201.032..1,201.032 rows=107,011 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 2 (originally 1) Memory Usage: 15361kB
  • Buffers: shared hit=4233 read=26699, temp read=3529 written=4683
22. 139.858 1,135.547 ↓ 1.5 107,011 1

Hash Left Join (cost=28,312.69..55,135.08 rows=73,408 width=146) (actual time=330.252..1,135.547 rows=107,011 loops=1)

  • Hash Cond: (invoices.order_item_id = order_items.id)
  • Buffers: shared hit=4233 read=26699, temp read=3529 written=3523
23. 670.836 697.689 ↓ 1.5 107,011 1

Bitmap Heap Scan on billing_abstract_invoices invoices (cost=5,114.41..26,081.11 rows=73,408 width=93) (actual time=29.470..697.689 rows=107,011 loops=1)

  • Recheck Cond: ((archived_at IS NULL) AND (voided_at IS NULL) AND (parent_invoice_id IS NULL))
  • Filter: (((type)::text = ANY ('{Billing::Invoice,Billing::ThirdPartyInvoice}'::text[])) AND ((state)::text <> 'reversed'::text) AND ((payer_type)::text = 'Billing::Insurer'::text))
  • Rows Removed by Filter: 209417
  • Heap Blocks: exact=17730
  • Buffers: shared hit=4231 read=14366
24. 26.853 26.853 ↓ 1.7 316,428 1

Bitmap Index Scan on active_invoices (cost=0.00..5,096.06 rows=184,611 width=0) (actual time=26.853..26.853 rows=316,428 loops=1)

  • Index Cond: ((archived_at IS NULL) AND (voided_at IS NULL) AND (parent_invoice_id IS NULL))
  • Buffers: shared hit=2 read=865
25. 128.755 298.000 ↑ 1.0 326,768 1

Hash (cost=15,602.68..15,602.68 rows=326,768 width=57) (actual time=298.000..298.000 rows=326,768 loops=1)

  • Buckets: 262144 Batches: 4 Memory Usage: 9332kB
  • Buffers: shared hit=2 read=12333, temp written=2367
26. 169.245 169.245 ↑ 1.0 326,768 1

Seq Scan on billing_order_items order_items (cost=0.00..15,602.68 rows=326,768 width=57) (actual time=0.369..169.245 rows=326,768 loops=1)

  • Buffers: shared hit=2 read=12333
27. 0.040 1.189 ↑ 1.0 137 1

Hash (cost=7.37..7.37 rows=137 width=26) (actual time=1.189..1.189 rows=137 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=1 read=5
28. 1.149 1.149 ↑ 1.0 137 1

Seq Scan on billing_insurers insurers (cost=0.00..7.37 rows=137 width=26) (actual time=0.368..1.149 rows=137 loops=1)

  • Buffers: shared hit=1 read=5
29. 51.172 166.772 ↓ 2.3 184,849 1

Sort (cost=14,145.99..14,347.94 rows=80,782 width=17) (actual time=145.464..166.772 rows=184,849 loops=1)

  • Sort Key: patient_users.patient_id
  • Sort Method: quicksort Memory: 8899kB
  • Buffers: shared read=4276
30. 12.525 115.600 ↓ 1.0 80,801 1

Subquery Scan on patient_users (cost=5,945.91..7,561.55 rows=80,782 width=17) (actual time=82.864..115.600 rows=80,801 loops=1)

  • Buffers: shared read=4276
31. 52.925 103.075 ↓ 1.0 80,801 1

HashAggregate (cost=5,945.91..6,753.73 rows=80,782 width=21) (actual time=82.863..103.075 rows=80,801 loops=1)

  • Group Key: pusers.id, pusers.patient_id, pusers.first_name, pusers.last_name
  • Buffers: shared read=4276
32. 50.150 50.150 ↓ 1.0 80,801 1

Seq Scan on users pusers (cost=0.00..5,138.09 rows=80,782 width=21) (actual time=0.684..50.150 rows=80,801 loops=1)

  • Filter: ((archived_at IS NULL) AND (patient_id IS NOT NULL))
  • Rows Removed by Filter: 5408
  • Buffers: shared read=4276
Planning time : 40.072 ms
Execution time : 7,320.199 ms