explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J35v

Settings
# exclusive inclusive rows x rows loops node
1. 1.367 5,384.766 ↑ 1.6 8,012 1

Subquery Scan on gi (cost=136,412.71..137,300.10 rows=12,677 width=315) (actual time=5,370.599..5,384.766 rows=8,012 loops=1)

  • Buffers: shared hit=247574 read=49855
2. 11.896 5,383.399 ↑ 1.6 8,012 1

GroupAggregate (cost=136,412.71..137,173.33 rows=12,677 width=332) (actual time=5,370.598..5,383.399 rows=8,012 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(pusers.first_name, ' ', pusers.last_name)), (string_agg(invoice_items.description, '
  • Buffers: shared hit=247574 read=49855
3. 21.423 5,371.503 ↑ 1.0 12,419 1

Sort (cost=136,412.71..136,444.40 rows=12,677 width=280) (actual time=5,370.577..5,371.503 rows=12,419 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(pusers.first_name, ' ', pusers.last_name)), (string_agg(invoice_items.description, '
  • Sort Method: quicksort Memory: 6799kB
  • Buffers: shared hit=247574 read=49855
4. 20.454 5,350.080 ↑ 1.0 12,419 1

Merge Left Join (cost=37,389.48..135,548.78 rows=12,677 width=280) (actual time=1,560.591..5,350.080 rows=12,419 loops=1)

  • Merge Cond: (invoices.id = ci.parent_invoice_id)
  • Buffers: shared hit=247571 read=49855
5. 72.828 4,541.165 ↑ 1.6 8,012 1

Merge Left Join (cost=28,213.33..126,308.95 rows=12,677 width=249) (actual time=774.409..4,541.165 rows=8,012 loops=1)

  • Merge Cond: (invoices.id = invoices_1.id)
  • Buffers: shared hit=242502 read=44778
6. 8.395 774.966 ↑ 1.6 8,012 1

Sort (cost=28,212.48..28,244.18 rows=12,677 width=185) (actual time=772.412..774.966 rows=8,012 loops=1)

  • Sort Key: invoices.id
  • Sort Method: quicksort Memory: 2321kB
  • Buffers: shared hit=55382 read=18420
7. 15.917 766.571 ↑ 1.6 8,012 1

Hash Right Join (cost=24,394.41..27,348.55 rows=12,677 width=185) (actual time=728.029..766.571 rows=8,012 loops=1)

  • Hash Cond: (pusers.patient_id = orders.patient_id)
  • Buffers: shared hit=55382 read=18420
8. 56.745 92.495 ↓ 1.0 80,801 1

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

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

Seq Scan on users pusers (cost=0.00..5,138.09 rows=80,782 width=21) (actual time=0.270..35.750 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
10. 7.466 658.159 ↓ 2.9 8,012 1

Hash (cost=18,413.58..18,413.58 rows=2,794 width=172) (actual time=658.159..658.159 rows=8,012 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1650kB
  • Buffers: shared hit=55382 read=14144
11. 5.265 650.693 ↓ 2.9 8,012 1

Gather (cost=1,063.02..18,413.58 rows=2,794 width=172) (actual time=6.475..650.693 rows=8,012 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=55382 read=14144
12. 108.772 645.428 ↓ 2.4 4,006 2

Nested Loop Left Join (cost=63.02..17,134.18 rows=1,644 width=172) (actual time=3.176..645.428 rows=4,006 loops=2)

  • Buffers: shared hit=55382 read=14144
13. 122.999 536.630 ↓ 2.4 4,006 2

Nested Loop Left Join (cost=62.59..16,344.36 rows=1,644 width=168) (actual time=2.983..536.630 rows=4,006 loops=2)

  • Buffers: shared hit=26334 read=11124
14. 3.759 413.602 ↓ 2.4 4,006 2

Hash Left Join (cost=62.17..7,597.53 rows=1,644 width=115) (actual time=2.782..413.602 rows=4,006 loops=2)

  • Hash Cond: (invoices.payer_id = insurers.id)
  • Join Filter: ((invoices.payer_type)::text = 'Billing::Insurer'::text)
  • Buffers: shared hit=50 read=5336
15. 406.302 409.462 ↓ 2.4 4,006 2

Parallel Bitmap Heap Scan on billing_abstract_invoices invoices (cost=54.45..7,576.43 rows=1,644 width=93) (actual time=2.280..409.462 rows=4,006 loops=2)

  • Recheck Cond: ((payer_id = 5) AND ((payer_type)::text = 'Billing::Insurer'::text) AND (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))
  • Heap Blocks: exact=2642
  • Buffers: shared read=5330
16. 3.160 3.160 ↓ 2.8 8,012 1

Bitmap Index Scan on foo (cost=0.00..53.75 rows=2,844 width=0) (actual time=3.160..3.160 rows=8,012 loops=1)

  • Index Cond: (payer_id = 5)
  • Buffers: shared read=25
17. 0.006 0.381 ↑ 1.0 1 2

Hash (cost=7.71..7.71 rows=1 width=26) (actual time=0.381..0.381 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6 read=6
18. 0.375 0.375 ↑ 1.0 1 2

Seq Scan on billing_insurers insurers (cost=0.00..7.71 rows=1 width=26) (actual time=0.362..0.375 rows=1 loops=2)

  • Filter: (id = 5)
  • Rows Removed by Filter: 136
  • Buffers: shared hit=6 read=6
19. 0.029 0.029 ↑ 1.0 1 8,012

Index Scan using billing_order_items_pkey on billing_order_items order_items (cost=0.42..5.32 rows=1 width=57) (actual time=0.029..0.029 rows=1 loops=8,012)

  • Index Cond: (id = invoices.order_item_id)
  • Buffers: shared hit=26284 read=5788
20. 0.026 0.026 ↑ 1.0 1 8,012

Index Scan using billing_orders_pkey on billing_orders orders (cost=0.42..0.48 rows=1 width=12) (actual time=0.026..0.026 rows=1 loops=8,012)

  • Index Cond: (id = order_items.order_id)
  • Buffers: shared hit=29048 read=3020
21. 2,139.440 3,693.371 ↑ 1.0 632,106 1

GroupAggregate (cost=0.85..90,004.76 rows=632,124 width=68) (actual time=0.633..3,693.371 rows=632,106 loops=1)

  • Group Key: invoices_1.id
  • Buffers: shared hit=187120 read=26358
22. 392.526 1,553.931 ↑ 1.0 839,332 1

Merge Left Join (cost=0.85..74,227.67 rows=839,365 width=136) (actual time=0.535..1,553.931 rows=839,332 loops=1)

  • Merge Cond: (invoices_1.id = invoice_items.invoice_id)
  • Buffers: shared hit=187115 read=26356
23. 106.232 106.232 ↑ 1.0 632,107 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.472..106.232 rows=632,107 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=1 read=1730
24. 1,055.173 1,055.173 ↑ 1.0 839,332 1

Index Scan using index_billing_invoice_items_on_invoice_id on billing_invoice_items invoice_items (cost=0.42..45,725.01 rows=839,365 width=136) (actual time=0.056..1,055.173 rows=839,332 loops=1)

  • Buffers: shared hit=187114 read=24626
25. 9.550 788.461 ↓ 8.2 16,969 1

Sort (cost=9,176.15..9,181.35 rows=2,081 width=16) (actual time=786.165..788.461 rows=16,969 loops=1)

  • Sort Key: ci.parent_invoice_id
  • Sort Method: quicksort Memory: 2007kB
  • Buffers: shared hit=5069 read=5077
26. 778.911 778.911 ↓ 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=0.899..778.911 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=5069 read=5077