explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a8Z9

Settings
# exclusive inclusive rows x rows loops node
1. 231.927 6,025.064 ↓ 10.6 16,009 1

Sort (cost=265,177.34..265,181.13 rows=1,517 width=653) (actual time=6,023.531..6,025.064 rows=16,009 loops=1)

  • Sort Key: nq_finance_company.finance_company_name, nq_ref_transaction_type.transaction_code_desc, (to_char(nq_transaction.transaction_date, 'MM/dd/yyyy'::text)), nq_base_contract.quote_no
  • Sort Method: quicksort Memory: 4586kB
2. 54.855 5,793.137 ↓ 10.6 16,009 1

Nested Loop Left Join (cost=15,773.61..265,097.18 rows=1,517 width=653) (actual time=1,682.185..5,793.137 rows=16,009 loops=1)

  • Join Filter: (nq_finance_company.finance_company_id = nq_finance_company_1.finance_company_id)
3. 0.021 0.021 ↑ 1.0 1 1

Index Scan using nq_finance_company_pkey on nq_finance_company (cost=0.14..8.16 rows=1 width=36) (actual time=0.014..0.021 rows=1 loops=1)

  • Index Cond: (finance_company_id = 526)
4. 34.773 5,738.261 ↓ 10.6 16,009 1

Nested Loop Left Join (cost=15,773.46..265,066.27 rows=1,517 width=601) (actual time=1,673.186..5,738.261 rows=16,009 loops=1)

5. 26.603 5,479.390 ↓ 10.6 16,007 1

Nested Loop Left Join (cost=15,773.03..264,339.71 rows=1,517 width=629) (actual time=1,664.794..5,479.390 rows=16,007 loops=1)

  • Join Filter: (nq_payment.payment_method_id = nq_ref_pmt_mthd_type.reference_code_id)
  • Rows Removed by Join Filter: 48325
  • Filter: ((((nq_ref_pmt_mthd_type.reference_code_name)::text <> 'Premium Refund'::text) AND ((nq_ref_pmt_mthd_type.reference_code_name)::text <> 'Payout'::text)) OR (nq_ref_pmt_mthd_type.reference_code_name IS NULL))
  • Rows Removed by Filter: 196
6. 24.850 5,452.787 ↓ 8.2 16,203 1

Nested Loop Left Join (cost=15,773.03..264,052.70 rows=1,979 width=633) (actual time=1,664.736..5,452.787 rows=16,203 loops=1)

7. 4.658 5,233.501 ↓ 8.2 16,203 1

Nested Loop (cost=15,772.61..263,131.40 rows=1,979 width=629) (actual time=1,656.973..5,233.501 rows=16,203 loops=1)

8. 0.042 0.042 ↑ 1.0 1 1

Index Only Scan using nq_finance_company_pkey on nq_finance_company nq_finance_company_1 (cost=0.14..8.16 rows=1 width=4) (actual time=0.026..0.042 rows=1 loops=1)

  • Index Cond: (finance_company_id = 526)
  • Heap Fetches: 1
9. 20.011 5,228.801 ↓ 8.2 16,203 1

Hash Join (cost=15,772.46..263,103.45 rows=1,979 width=629) (actual time=1,656.933..5,228.801 rows=16,203 loops=1)

  • Hash Cond: ((nq_transaction.modifiedby)::text = (s.user_name)::text)
10. 0.000 5,174.573 ↓ 8.1 16,211 1

Gather Merge (cost=14,494.33..261,798.01 rows=2,007 width=637) (actual time=1,622.675..5,174.573 rows=16,211 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 3.538 5,181.265 ↓ 6.5 5,404 3 / 3

Nested Loop (cost=13,494.31..260,566.33 rows=836 width=637) (actual time=1,603.645..5,181.265 rows=5,404 loops=3)

12. 9.085 5,161.516 ↓ 6.5 5,404 3 / 3

Nested Loop (cost=13,494.03..260,314.08 rows=836 width=612) (actual time=1,603.596..5,161.516 rows=5,404 loops=3)

13. 160.414 5,092.991 ↓ 6.5 5,404 3 / 3

Merge Join (cost=13,493.61..259,858.19 rows=836 width=585) (actual time=1,595.038..5,092.991 rows=5,404 loops=3)

  • Merge Cond: (nq_transaction.transaction_id = nq_gl_account_entry.transaction_id)
14. 11.481 1,399.499 ↓ 2.5 5,404 3 / 3

Sort (cost=13,493.17..13,498.55 rows=2,151 width=553) (actual time=1,396.174..1,399.499 rows=5,404 loops=3)

  • Sort Key: nq_transaction.transaction_id
  • Sort Method: quicksort Memory: 986kB
  • Worker 0: Sort Method: quicksort Memory: 918kB
  • Worker 1: Sort Method: quicksort Memory: 953kB
15. 23.984 1,388.018 ↓ 2.5 5,404 3 / 3

Hash Join (cost=17.42..13,374.11 rows=2,151 width=553) (actual time=27.283..1,388.018 rows=5,404 loops=3)

  • Hash Cond: ((nq_transaction.transaction_type)::numeric = (nq_ref_transaction_type.transaction_code_id)::numeric)
16. 15.451 1,363.948 ↑ 2.5 24,974 3 / 3

Nested Loop (cost=15.49..12,966.62 rows=61,448 width=41) (actual time=26.991..1,363.948 rows=24,974 loops=3)

17. 15.191 44.626 ↑ 4.0 632 3 / 3

Hash Join (cost=15.06..6,430.47 rows=2,503 width=21) (actual time=0.482..44.626 rows=632 loops=3)

  • Hash Cond: (nq_base_contract.finance_plan_id = nq_finance_plan.finance_plan_id)
18. 29.092 29.092 ↑ 1.3 52,016 3 / 3

Parallel Seq Scan on nq_base_contract (cost=0.00..6,242.73 rows=65,073 width=21) (actual time=0.020..29.092 rows=52,016 loops=3)

19. 0.013 0.343 ↑ 1.2 13 3 / 3

Hash (cost=14.88..14.88 rows=15 width=8) (actual time=0.342..0.343 rows=13 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.330 0.330 ↑ 1.2 13 3 / 3

Seq Scan on nq_finance_plan (cost=0.00..14.88 rows=15 width=8) (actual time=0.117..0.330 rows=13 loops=3)

  • Filter: (finance_company_id = 526)
  • Rows Removed by Filter: 346
21. 1,303.871 1,303.871 ↓ 1.2 39 1,897 / 3

Index Scan using trns_trns_rel_idx on nq_transaction (cost=0.43..2.29 rows=32 width=28) (actual time=1.769..2.062 rows=39 loops=1,897)

  • Index Cond: (transaction_relation_id = nq_base_contract.transaction_relation_id)
22. 0.023 0.086 ↑ 1.2 6 3 / 3

Hash (cost=1.84..1.84 rows=7 width=520) (actual time=0.086..0.086 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.063 0.063 ↑ 1.2 6 3 / 3

Seq Scan on nq_ref_transaction_type (cost=0.00..1.84 rows=7 width=520) (actual time=0.047..0.063 rows=6 loops=3)

  • Filter: (((transaction_code_desc)::text = 'Apply Chargeback'::text) OR ((transaction_code_desc)::text = 'Apply Payment'::text) OR ((transaction_code_desc)::text = 'Apply Premium Refund'::text) OR ((transaction_code_desc)::text = 'New Negative Endorsement'::text) OR ((transaction_code_desc)::text = 'Payout Contract'::text) OR ((transaction_code_desc)::text = 'Reverse Payment'::text) OR ((transaction_code_desc)::text = 'Premium Refund Reversal'::text) OR ((transaction_code_desc)::text = 'Payout Reversal'::text))
  • Rows Removed by Filter: 22
24. 1,494.996 3,533.078 ↑ 1.0 1,481,081 3 / 3

GroupAggregate (cost=0.43..227,706.38 rows=1,491,161 width=36) (actual time=0.095..3,533.078 rows=1,481,081 loops=3)

  • Group Key: nq_gl_account_entry.transaction_id
25. 2,038.082 2,038.082 ↓ 1.1 1,836,600 3 / 3

Index Scan using nq_gl_account_entry_transactionid on nq_gl_account_entry (cost=0.43..191,602.11 rows=1,746,476 width=14) (actual time=0.068..2,038.082 rows=1,836,600 loops=3)

  • Filter: ((debit_id = 1100) OR (credit_id = 1100))
  • Rows Removed by Filter: 2829297
26. 59.440 59.440 ↑ 1.0 1 16,211 / 3

Index Scan using nq_client_pkey on nq_client (cost=0.42..0.55 rows=1 width=35) (actual time=0.011..0.011 rows=1 loops=16,211)

  • Index Cond: (client_id = nq_base_contract.client_id)
27. 16.211 16.211 ↑ 1.0 1 16,211 / 3

Index Scan using nq_brokerage_pkey on nq_brokerage (cost=0.28..0.30 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=16,211)

  • Index Cond: (brokerage_id = nq_client.brokerage_id)
28. 3.564 34.217 ↓ 1.0 10,134 1

Hash (cost=1,151.72..1,151.72 rows=10,113 width=9) (actual time=34.217..34.217 rows=10,134 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
29. 5.820 30.653 ↓ 1.0 10,134 1

Hash Join (cost=810.38..1,151.72 rows=10,113 width=9) (actual time=21.371..30.653 rows=10,134 loops=1)

  • Hash Cond: (nq_admin_user.user_id = s.contact_id)
30. 10.407 15.989 ↓ 1.0 10,138 1

HashAggregate (cost=445.99..547.13 rows=10,114 width=1,040) (actual time=12.449..15.989 rows=10,138 loops=1)

  • Group Key: nq_admin_user.user_id, nq_admin_user.group_id, nq_admin_user.first_name, nq_admin_user.last_name
31. 1.398 5.582 ↓ 1.0 10,139 1

Append (cost=0.00..344.85 rows=10,114 width=1,040) (actual time=0.034..5.582 rows=10,139 loops=1)

32. 0.045 0.045 ↑ 1.0 44 1

Seq Scan on nq_admin_user (cost=0.00..1.44 rows=44 width=1,040) (actual time=0.033..0.045 rows=44 loops=1)

33. 0.253 0.253 ↓ 1.0 530 1

Seq Scan on nq_finco_user (cost=0.00..10.11 rows=511 width=21) (actual time=0.025..0.253 rows=530 loops=1)

34. 3.886 3.886 ↓ 1.0 9,565 1

Seq Scan on nq_broker (cost=0.00..181.59 rows=9,559 width=20) (actual time=0.026..3.886 rows=9,565 loops=1)

35. 3.621 8.844 ↑ 1.0 10,138 1

Hash (cost=236.20..236.20 rows=10,255 width=13) (actual time=8.844..8.844 rows=10,138 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 580kB
36. 5.223 5.223 ↑ 1.0 10,138 1

Seq Scan on nq_system_user s (cost=0.00..236.20 rows=10,255 width=13) (actual time=0.046..5.223 rows=10,138 loops=1)

  • Filter: (user_id <> 1)
  • Rows Removed by Filter: 1
37. 194.436 194.436 ↑ 1.0 1 16,203

Index Scan using nq_payment_pkey on nq_payment (cost=0.43..0.47 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=16,203)

  • Index Cond: (nq_transaction.transaction_id = transaction_id)
38. 0.000 0.000 ↑ 2.0 4 16,203

Materialize (cost=0.00..1.12 rows=8 width=520) (actual time=0.000..0.000 rows=4 loops=16,203)

39. 0.032 0.032 ↑ 1.0 8 1

Seq Scan on nq_payment_method_type nq_ref_pmt_mthd_type (cost=0.00..1.08 rows=8 width=520) (actual time=0.026..0.032 rows=8 loops=1)

40. 224.098 224.098 ↑ 1.0 1 16,007

Index Scan using nq_deposit_transactionid on nq_deposit (cost=0.43..0.47 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=16,007)

  • Index Cond: (nq_transaction.transaction_id = transaction_id)
  • Filter: (amount > '0'::double precision)
  • Rows Removed by Filter: 0
Planning time : 187.080 ms
Execution time : 6,028.152 ms