explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x2yf

Settings
# exclusive inclusive rows x rows loops node
1. 47.916 8,901.470 ↑ 1.0 15 1

Limit (cost=28,548.27..28,549.92 rows=15 width=2,212) (actual time=8,853.534..8,901.470 rows=15 loops=1)

2. 0.000 8,853.554 ↑ 48.9 15 1

Group (cost=28,548.27..28,628.90 rows=733 width=2,212) (actual time=8,853.532..8,853.554 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. 67.051 8,901.429 ↑ 40.7 15 1

Gather Merge (cost=28,548.27..28,622.49 rows=610 width=2,211) (actual time=8,853.527..8,901.429 rows=15 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.020 8,834.378 ↑ 10.9 28 3 / 3

Group (cost=27,548.25..27,552.06 rows=305 width=2,211) (actual time=8,834.354..8,834.378 rows=28 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.048 8,834.358 ↑ 10.9 28 3 / 3

Sort (cost=27,548.25..27,549.01 rows=305 width=2,211) (actual time=8,834.352..8,834.358 rows=28 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: 25kB
6. 209.680 8,834.310 ↑ 10.9 28 3 / 3

Hash Join (cost=25,602.52..27,535.66 rows=305 width=2,211) (actual time=8,834.175..8,834.310 rows=28 loops=3)

  • Hash Cond: (transactions_sub_payments.payment_id = transactions_payments.id)
7. 742.786 8,624.448 ↓ 3.9 241,108 3 / 3

Nested Loop (cost=15,740.73..17,512.83 rows=61,058 width=2,207) (actual time=5,722.893..8,624.448 rows=241,108 loops=3)

8. 462.623 6,448.768 ↓ 21,710.5 238,816 3 / 3

Merge Left Join (cost=15,740.30..15,740.68 rows=11 width=2,207) (actual time=5,716.545..6,448.768 rows=238,816 loops=3)

  • Merge Cond: (transactions_invoices.id = insurance_claims.invoice_id)
9. 993.372 5,986.062 ↓ 21,710.4 238,814 3 / 3

Sort (cost=15,735.79..15,735.81 rows=11 width=2,203) (actual time=5,716.469..5,986.062 rows=238,814 loops=3)

  • Sort Key: transactions_invoices.id
  • Sort Method: external merge Disk: 25432kB
10. 839.757 4,992.690 ↓ 21,710.4 238,814 3 / 3

Nested Loop (cost=0.85..15,735.60 rows=11 width=2,203) (actual time=0.048..4,992.690 rows=238,814 loops=3)

11. 727.475 2,720.051 ↓ 21,710.4 238,814 3 / 3

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

12. 320.880 320.880 ↓ 160.1 238,814 3 / 3

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

  • Filter: (payment_receiver_id = 1)
13. 1,671.696 1,671.696 ↑ 27.0 1 716,441 / 3

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: 29
14. 1,432.882 1,432.882 ↑ 1.0 1 716,441 / 3

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
15. 0.042 0.083 ↑ 1.5 43 3 / 3

Sort (cost=4.51..4.67 rows=63 width=8) (actual time=0.071..0.083 rows=43 loops=3)

  • Sort Key: insurance_claims.invoice_id
  • Sort Method: quicksort Memory: 27kB
16. 0.041 0.041 ↑ 1.0 63 3 / 3

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

17. 1,432.894 1,432.894 ↑ 5,427.0 1 716,447 / 3

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)
18. 0.069 0.182 ↑ 24.9 218 3 / 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 74kB
19. 0.090 0.113 ↑ 24.9 218 3 / 3

Bitmap Heap Scan on transactions_payments (cost=12.77..9,794.05 rows=5,419 width=12) (actual time=0.030..0.113 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
20. 0.023 0.023 ↑ 24.9 218 3 / 3

Bitmap Index Scan on payment_idx (cost=0.00..11.42 rows=5,419 width=0) (actual time=0.022..0.023 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.112 ms
Execution time : 8,906.051 ms