explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4mnE

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.964 8,811.240 ↑ 1.0 15 1

Limit (cost=21,223.26..21,224.91 rows=15 width=2,212) (actual time=8,809.234..8,811.240 rows=15 loops=1)

2. 0.000 8,809.276 ↑ 48.9 15 1

Group (cost=21,223.26..21,303.89 rows=733 width=2,212) (actual time=8,809.233..8,809.276 rows=15 loops=1)

  • Group Key: (CASE WHEN ((transactions_invoices.type)::text = 'Transactions::MonthlyInvoice'::text) THEN transactions_invoices.created_at ELSE transactions_payments.created_at END), transactions_invoices.id, insurance_claims.id
3. 0.000 8,811.196 ↑ 40.7 15 1

Gather Merge (cost=21,223.26..21,297.48 rows=610 width=2,211) (actual time=8,809.227..8,811.196 rows=15 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.045 26,301.753 ↑ 17.9 17 3

Group (cost=20,223.24..20,227.05 rows=305 width=2,211) (actual time=8,767.233..8,767.251 rows=17 loops=3)

  • Group Key: (CASE WHEN ((transactions_invoices.type)::text = 'Transactions::MonthlyInvoice'::text) THEN transactions_invoices.created_at ELSE transactions_payments.created_at END), transactions_invoices.id, insurance_claims.id
5. 0.150 26,301.708 ↑ 17.9 17 3

Sort (cost=20,223.24..20,224.00 rows=305 width=2,211) (actual time=8,767.231..8,767.236 rows=17 loops=3)

  • Sort Key: (CASE WHEN ((transactions_invoices.type)::text = 'Transactions::MonthlyInvoice'::text) THEN transactions_invoices.created_at ELSE transactions_payments.created_at END) DESC, transactions_invoices.id, insurance_claims.id
  • Sort Method: quicksort Memory: 31kB
6. 609.780 26,301.558 ↑ 10.9 28 3

Hash Join (cost=18,277.51..20,210.65 rows=305 width=2,211) (actual time=8,767.050..8,767.186 rows=28 loops=3)

  • Hash Cond: (transactions_sub_payments.payment_id = transactions_payments.id)
7. 2,500.245 25,691.268 ↓ 3.9 241,108 3

Nested Loop (cost=8,415.72..10,187.83 rows=61,058 width=2,207) (actual time=5,525.020..8,563.756 rows=241,108 loops=3)

8. 1,421.967 18,892.341 ↓ 21,710.5 238,816 3

Merge Left Join (cost=8,415.29..8,415.67 rows=11 width=2,207) (actual time=5,525.002..6,297.447 rows=238,816 loops=3)

  • Merge Cond: (transactions_invoices.id = insurance_claims.invoice_id)
9. 3,345.750 17,470.095 ↓ 21,710.4 238,814 3

Sort (cost=8,410.78..8,410.81 rows=11 width=2,203) (actual time=5,524.915..5,823.365 rows=238,814 loops=3)

  • Sort Key: transactions_invoices.id
  • Sort Method: external merge Disk: 25288kB
10. 1,920.906 14,124.345 ↓ 21,710.4 238,814 3

Nested Loop (cost=69.04..8,410.59 rows=11 width=2,203) (actual time=35.552..4,708.115 rows=238,814 loops=3)

11. 2,112.940 7,904.793 ↓ 21,710.4 238,814 3

Nested Loop (cost=68.61..8,390.84 rows=11 width=2,203) (actual time=35.536..2,634.931 rows=238,814 loops=3)

12. 744.016 776.766 ↓ 160.1 238,814 3

Parallel Bitmap Heap Scan on transactions_invoices (cost=68.19..7,563.46 rows=1,492 width=2,199) (actual time=35.496..258.922 rows=238,814 loops=3)

  • Recheck Cond: (payment_receiver_id = 1)
  • Heap Blocks: exact=3708
13. 32.750 32.750 ↓ 200.0 716,441 1

Bitmap Index Scan on transactions_invoices_idx (cost=0.00..67.29 rows=3,582 width=0) (actual time=32.749..32.750 rows=716,441 loops=1)

  • Index Cond: (payment_receiver_id = 1)
14. 5,015.087 5,015.087 ↑ 27.0 1 716,441

Index Only Scan using index_tis_on_owner_id_and_owner_type on transactions_invoice_sequences (cost=0.43..3.86 rows=27 width=4) (actual time=0.007..0.007 rows=1 loops=716,441)

  • Index Cond: ((owner_id = transactions_invoices.id) AND (owner_type = 'Transactions::Invoice'::text))
  • Heap Fetches: 0
15. 4,298.646 4,298.646 ↑ 1.0 1 716,441

Index Only Scan using customers_pkey on customers (cost=0.42..1.80 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=716,441)

  • Index Cond: (id = transactions_invoices.customer_id)
  • Heap Fetches: 0
16. 0.138 0.279 ↑ 1.3 47 3

Sort (cost=4.51..4.67 rows=63 width=8) (actual time=0.081..0.093 rows=47 loops=3)

  • Sort Key: insurance_claims.invoice_id
  • Sort Method: quicksort Memory: 27kB
17. 0.141 0.141 ↑ 1.0 63 3

Seq Scan on insurance_claims (cost=0.00..2.63 rows=63 width=8) (actual time=0.024..0.047 rows=63 loops=3)

18. 4,298.682 4,298.682 ↑ 5,427.0 1 716,447

Index Scan using index_transactions_sub_payments_on_invoice_id on transactions_sub_payments (cost=0.43..106.83 rows=5,427 width=8) (actual time=0.005..0.006 rows=1 loops=716,447)

  • Index Cond: (invoice_id = transactions_invoice_sequences.owner_id)
19. 0.201 0.510 ↑ 24.9 218 3

Hash (cost=9,794.05..9,794.05 rows=5,419 width=12) (actual time=0.170..0.170 rows=218 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 74kB
20. 0.249 0.309 ↑ 24.9 218 3

Bitmap Heap Scan on transactions_payments (cost=12.77..9,794.05 rows=5,419 width=12) (actual time=0.026..0.103 rows=218 loops=3)

  • Recheck Cond: ((created_at >= '2019-03-30 22:00:00'::timestamp without time zone) AND (created_at <= '2019-10-25 21:59:59.999999'::timestamp without time zone))
  • Heap Blocks: exact=14
21. 0.060 0.060 ↑ 24.9 218 3

Bitmap Index Scan on payment_idx (cost=0.00..11.42 rows=5,419 width=0) (actual time=0.020..0.020 rows=218 loops=3)

  • Index Cond: ((created_at >= '2019-03-30 22:00:00'::timestamp without time zone) AND (created_at <= '2019-10-25 21:59:59.999999'::timestamp without time zone))
Planning time : 2.186 ms
Execution time : 8,814.349 ms