explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hThJ : Optimization for: plan #4mnE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.081 94.521 ↑ 1.0 15 1

Limit (cost=22,065.49..22,067.14 rows=15 width=167) (actual time=94.403..94.521 rows=15 loops=1)

2. 0.000 94.440 ↑ 21.9 15 1

Group (cost=22,065.49..22,101.70 rows=329 width=167) (actual time=94.401..94.440 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 94.481 ↑ 18.3 15 1

Gather Merge (cost=22,065.49..22,098.82 rows=274 width=166) (actual time=94.397..94.481 rows=15 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.018 237.288 ↑ 27.4 5 3

Group (cost=21,065.46..21,067.17 rows=137 width=166) (actual time=79.090..79.096 rows=5 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.135 237.270 ↑ 27.4 5 3

Sort (cost=21,065.46..21,065.80 rows=137 width=166) (actual time=79.089..79.090 rows=5 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: 36kB
6. 0.078 237.135 ↑ 4.9 28 3

Hash Left Join (cost=5.12..21,060.60 rows=137 width=166) (actual time=78.527..79.045 rows=28 loops=3)

  • Hash Cond: (transactions_invoices.id = insurance_claims.invoice_id)
7. 0.063 236.982 ↑ 4.9 28 3

Nested Loop (cost=1.70..21,055.80 rows=137 width=162) (actual time=78.496..78.994 rows=28 loops=3)

8. 0.078 236.751 ↑ 4.9 28 3

Nested Loop (cost=1.28..20,994.59 rows=137 width=162) (actual time=78.492..78.917 rows=28 loops=3)

  • Join Filter: (transactions_invoice_sequences.owner_id = transactions_invoices.id)
9. 0.119 236.505 ↑ 5.2 28 3

Nested Loop (cost=0.85..20,920.23 rows=147 width=16) (actual time=78.487..78.835 rows=28 loops=3)

10. 0.269 236.139 ↑ 2.9 82 3

Nested Loop (cost=0.43..20,809.33 rows=235 width=12) (actual time=48.687..78.713 rows=82 loops=3)

11. 235.434 235.434 ↑ 3.2 73 3

Parallel Seq Scan on transactions_payments (cost=0.00..18,930.79 rows=234 width=12) (actual time=48.683..78.478 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
12. 0.436 0.436 ↑ 1.0 1 218

Index Scan using index_transactions_sub_payments_on_payment_id on transactions_sub_payments (cost=0.43..8.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=218)

  • Index Cond: (payment_id = transactions_payments.id)
13. 0.247 0.247 ↓ 0.0 0 247

Index Only Scan using index_tis_on_owner_id_and_owner_type on transactions_invoice_sequences (cost=0.43..0.47 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=247)

  • Index Cond: ((owner_id = transactions_sub_payments.invoice_id) AND (owner_type = 'Transactions::Invoice'::text))
  • Heap Fetches: 0
14. 0.168 0.168 ↑ 1.0 1 84

Index Scan using transactions_invoices_pkey on transactions_invoices (cost=0.42..0.49 rows=1 width=154) (actual time=0.002..0.002 rows=1 loops=84)

  • Index Cond: (id = transactions_sub_payments.invoice_id)
  • Filter: (payment_receiver_id = 1)
15. 0.168 0.168 ↑ 1.0 1 84

Index Only Scan using customers_pkey on customers (cost=0.42..0.45 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=84)

  • Index Cond: (id = transactions_invoices.customer_id)
  • Heap Fetches: 0
16. 0.022 0.075 ↑ 1.0 63 1

Hash (cost=2.63..2.63 rows=63 width=8) (actual time=0.074..0.075 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.053 0.053 ↑ 1.0 63 1

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

Planning time : 2.507 ms
Execution time : 94.644 ms