explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YK5L

Settings
# exclusive inclusive rows x rows loops node
1. 0.124 29,881.054 ↑ 1.0 15 1

Limit (cost=42,078.20..42,078.39 rows=15 width=2,212) (actual time=29,880.916..29,881.054 rows=15 loops=1)

2. 0.014 29,880.930 ↑ 48.9 15 1

Group (cost=42,078.20..42,087.36 rows=733 width=2,212) (actual time=29,880.914..29,880.930 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.098 29,880.916 ↑ 48.9 15 1

Sort (cost=42,078.20..42,080.03 rows=733 width=2,211) (actual time=29,880.912..29,880.916 rows=15 loops=1)

  • 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: 36kB
4. 364.507 29,880.818 ↑ 8.6 85 1

Hash Join (cost=21,541.67..42,043.32 rows=733 width=2,211) (actual time=29,857.588..29,880.818 rows=85 loops=1)

  • Hash Cond: (transactions_sub_payments.payment_id = transactions_payments.id)
5. 1,314.860 29,417.031 ↓ 4.9 723,324 1

Nested Loop (cost=1,001.28..21,116.43 rows=146,540 width=2,207) (actual time=0.559..29,417.031 rows=723,324 loops=1)

6. 13,876.264 25,952.830 ↓ 26,535.1 716,447 1

Nested Loop Left Join (cost=1,000.85..16,766.60 rows=27 width=2,207) (actual time=0.542..25,952.830 rows=716,447 loops=1)

  • Join Filter: (insurance_claims.invoice_id = transactions_invoices.id)
  • Rows Removed by Join Filter: 45135720
7. 0.000 613.510 ↓ 26,534.9 716,441 1

Gather (cost=1,000.85..16,738.30 rows=27 width=2,203) (actual time=0.470..613.510 rows=716,441 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 772.176 1,684.568 ↓ 21,710.4 238,814 3

Nested Loop (cost=0.85..15,735.60 rows=11 width=2,203) (actual time=0.037..1,684.568 rows=238,814 loops=3)

9. 798.049 912.390 ↓ 21,710.4 238,814 3

Nested Loop (cost=0.43..15,715.84 rows=11 width=2,203) (actual time=0.024..912.390 rows=238,814 loops=3)

10. 114.339 114.339 ↓ 160.1 238,814 3

Parallel Seq Scan on transactions_invoices (cost=0.00..14,888.46 rows=1,492 width=2,199) (actual time=0.004..114.339 rows=238,814 loops=3)

  • Filter: (payment_receiver_id = 1)
11. 0.002 0.002 ↑ 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.002..0.002 rows=1 loops=716,441)

  • Index Cond: ((owner_id = transactions_invoices.id) AND (owner_type = 'Transactions::Invoice'::text))
  • Heap Fetches: 0
12. 0.002 0.002 ↑ 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.002..0.002 rows=1 loops=716,441)

  • Index Cond: (id = transactions_invoices.customer_id)
  • Heap Fetches: 0
13. 11,463.029 11,463.056 ↑ 1.0 63 716,441

Materialize (cost=0.00..2.94 rows=63 width=8) (actual time=0.000..0.016 rows=63 loops=716,441)

14. 0.027 0.027 ↑ 1.0 63 1

Seq Scan on insurance_claims (cost=0.00..2.63 rows=63 width=8) (actual time=0.004..0.027 rows=63 loops=1)

15. 2,149.341 2,149.341 ↑ 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.003..0.003 rows=1 loops=716,447)

  • Index Cond: (invoice_id = transactions_invoice_sequences.owner_id)
16. 0.058 99.280 ↑ 24.9 218 1

Hash (cost=20,472.65..20,472.65 rows=5,419 width=12) (actual time=99.280..99.280 rows=218 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 74kB
17. 7.773 99.222 ↑ 24.9 218 1

Gather (cost=1,000.00..20,472.65 rows=5,419 width=12) (actual time=0.243..99.222 rows=218 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 91.449 91.449 ↑ 30.9 73 3

Parallel Seq Scan on transactions_payments (cost=0.00..18,930.75 rows=2,258 width=12) (actual time=39.520..91.449 rows=73 loops=3)

  • Filter: ((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))
  • Rows Removed by Filter: 361196
Planning time : 3.238 ms
Execution time : 29,881.181 ms