explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qe7

Settings
# exclusive inclusive rows x rows loops node
1. 4.883 3,088.514 ↓ 23.0 806 1

Sort (cost=261,818.52..261,818.61 rows=35 width=653) (actual time=3,088.469..3,088.514 rows=806 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: 238kB
2. 2.070 3,083.631 ↓ 23.0 806 1

Nested Loop Left Join (cost=15,300.55..261,817.62 rows=35 width=653) (actual time=2,851.564..3,083.631 rows=806 loops=1)

  • Join Filter: (nq_finance_company.finance_company_id = nq_finance_company_1.finance_company_id)
3. 0.024 0.024 ↑ 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.018..0.024 rows=1 loops=1)

  • Index Cond: (finance_company_id = 526)
4. 1.789 3,081.537 ↓ 23.0 806 1

Nested Loop Left Join (cost=15,300.40..261,808.93 rows=35 width=601) (actual time=2,851.514..3,081.537 rows=806 loops=1)

5. 0.885 3,076.524 ↓ 23.0 806 1

Nested Loop Left Join (cost=15,299.97..261,759.83 rows=35 width=629) (actual time=2,851.468..3,076.524 rows=806 loops=1)

  • 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: 16
6. 0.840 3,074.817 ↓ 18.3 822 1

Nested Loop Left Join (cost=15,299.84..261,752.55 rows=45 width=633) (actual time=2,851.435..3,074.817 rows=822 loops=1)

7. 0.211 3,071.511 ↓ 18.3 822 1

Nested Loop (cost=15,299.41..261,691.78 rows=45 width=629) (actual time=2,851.403..3,071.511 rows=822 loops=1)

8. 0.025 0.025 ↑ 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.010..0.025 rows=1 loops=1)

  • Index Cond: (finance_company_id = 526)
  • Heap Fetches: 1
9. 0.559 3,071.275 ↓ 18.3 822 1

Nested Loop (cost=15,299.27..261,683.17 rows=45 width=629) (actual time=2,851.387..3,071.275 rows=822 loops=1)

10. 0.933 3,069.072 ↓ 18.3 822 1

Nested Loop (cost=15,298.99..261,669.59 rows=45 width=604) (actual time=2,851.356..3,069.072 rows=822 loops=1)

11. 128.159 3,064.851 ↓ 18.3 822 1

Merge Join (cost=15,298.57..261,645.05 rows=45 width=577) (actual time=2,851.293..3,064.851 rows=822 loops=1)

  • Merge Cond: (nq_transaction.transaction_id = nq_gl_account_entry.transaction_id)
12. 0.932 174.834 ↓ 7.1 822 1

Sort (cost=15,298.13..15,298.42 rows=115 width=545) (actual time=174.555..174.834 rows=822 loops=1)

  • Sort Key: nq_transaction.transaction_id
  • Sort Method: quicksort Memory: 93kB
13. 0.569 173.902 ↓ 7.1 822 1

Hash Join (cost=2,295.55..15,294.20 rows=115 width=545) (actual time=167.165..173.902 rows=822 loops=1)

  • Hash Cond: ((nq_transaction.modifiedby)::text = (s.user_name)::text)
14. 0.000 141.304 ↓ 7.0 822 1

Gather (cost=1,017.42..14,014.48 rows=117 width=553) (actual time=135.070..141.304 rows=822 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 1.324 154.131 ↓ 5.6 274 3 / 3

Hash Join (cost=17.42..13,002.78 rows=49 width=553) (actual time=126.457..154.131 rows=274 loops=3)

  • Hash Cond: ((nq_transaction.transaction_type)::numeric = (nq_ref_transaction_type.transaction_code_id)::numeric)
16. 1.055 152.730 ↑ 1.4 1,013 3 / 3

Nested Loop (cost=15.49..12,991.65 rows=1,394 width=41) (actual time=125.102..152.730 rows=1,013 loops=3)

17. 14.862 39.752 ↑ 4.0 632 3 / 3

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

  • Hash Cond: (nq_base_contract.finance_plan_id = nq_finance_plan.finance_plan_id)
18. 24.575 24.575 ↑ 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.016..24.575 rows=52,016 loops=3)

19. 0.012 0.315 ↑ 1.2 13 3 / 3

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

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

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

  • Filter: (finance_company_id = 526)
  • Rows Removed by Filter: 346
21. 111.923 111.923 ↓ 2.0 2 1,897 / 3

Index Scan using trns_trns_rel_idx on nq_transaction (cost=0.43..2.61 rows=1 width=28) (actual time=0.164..0.177 rows=2 loops=1,897)

  • Index Cond: (transaction_relation_id = nq_base_contract.transaction_relation_id)
  • Filter: ((transaction_date >= to_date('01/01/2019'::text, 'MM/dd/yyyy'::text)) AND (transaction_date <= to_date('12/31/2019'::text, 'MM/dd/yyyy'::text)))
  • Rows Removed by Filter: 38
22. 0.020 0.077 ↑ 1.2 6 3 / 3

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

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

Seq Scan on nq_ref_transaction_type (cost=0.00..1.84 rows=7 width=520) (actual time=0.041..0.057 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. 4.141 32.029 ↓ 1.0 10,134 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
25. 5.803 27.888 ↓ 1.0 10,134 1

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

  • Hash Cond: (nq_admin_user.user_id = s.contact_id)
26. 10.454 15.356 ↓ 1.0 10,138 1

HashAggregate (cost=445.99..547.13 rows=10,114 width=1,040) (actual time=11.629..15.356 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
27. 1.543 4.902 ↓ 1.0 10,139 1

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

28. 0.026 0.026 ↑ 1.0 44 1

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

29. 0.171 0.171 ↓ 1.0 530 1

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

30. 3.162 3.162 ↓ 1.0 9,565 1

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

31. 3.363 6.729 ↑ 1.0 10,138 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 580kB
32. 3.366 3.366 ↑ 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.020..3.366 rows=10,138 loops=1)

  • Filter: (user_id <> 1)
  • Rows Removed by Filter: 1
33. 1,336.339 2,761.858 ↑ 1.0 1,470,724 1

GroupAggregate (cost=0.43..227,706.38 rows=1,491,161 width=36) (actual time=0.045..2,761.858 rows=1,470,724 loops=1)

  • Group Key: nq_gl_account_entry.transaction_id
34. 1,425.519 1,425.519 ↓ 1.0 1,823,870 1

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.031..1,425.519 rows=1,823,870 loops=1)

  • Filter: ((debit_id = 1100) OR (credit_id = 1100))
  • Rows Removed by Filter: 2809038
35. 3.288 3.288 ↑ 1.0 1 822

Index Scan using nq_client_pkey on nq_client (cost=0.42..0.55 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=822)

  • Index Cond: (client_id = nq_base_contract.client_id)
36. 1.644 1.644 ↑ 1.0 1 822

Index Scan using nq_brokerage_pkey on nq_brokerage (cost=0.28..0.30 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=822)

  • Index Cond: (brokerage_id = nq_client.brokerage_id)
37. 2.466 2.466 ↑ 1.0 1 822

Index Scan using nq_payment_pkey on nq_payment (cost=0.43..1.35 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=822)

  • Index Cond: (nq_transaction.transaction_id = transaction_id)
38. 0.822 0.822 ↑ 1.0 1 822

Index Scan using nq_ref_pmt_mthd_type_pkey on nq_payment_method_type nq_ref_pmt_mthd_type (cost=0.13..0.15 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=822)

  • Index Cond: (nq_payment.payment_method_id = reference_code_id)
39. 3.224 3.224 ↑ 1.0 1 806

Index Scan using nq_deposit_transactionid on nq_deposit (cost=0.43..1.39 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=806)

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