explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XYAv

Settings
# exclusive inclusive rows x rows loops node
1. 4.970 2,169.434 ↓ 20.7 806 1

Sort (cost=132,348.52..132,348.62 rows=39 width=653) (actual time=2,169.388..2,169.434 rows=806 loops=1)

  • Sort Key: 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.725 2,164.464 ↓ 20.7 806 1

Nested Loop (cost=15,302.99..132,347.49 rows=39 width=653) (actual time=1,922.806..2,164.464 rows=806 loops=1)

3. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: (finance_company_id = 526)
4. 2.115 2,161.728 ↓ 20.7 806 1

Nested Loop Left Join (cost=15,302.85..132,338.84 rows=39 width=601) (actual time=1,922.776..2,161.728 rows=806 loops=1)

5. 1.432 2,155.583 ↓ 20.7 806 1

Nested Loop Left Join (cost=15,302.42..132,284.12 rows=39 width=629) (actual time=1,922.749..2,155.583 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.859 2,153.329 ↓ 16.1 822 1

Nested Loop Left Join (cost=15,302.29..132,275.86 rows=51 width=633) (actual time=1,922.736..2,153.329 rows=822 loops=1)

7. 1.003 2,149.182 ↓ 16.1 822 1

Nested Loop (cost=15,301.86..132,206.98 rows=51 width=629) (actual time=1,922.721..2,149.182 rows=822 loops=1)

8. 1.175 2,148.179 ↓ 16.1 822 1

Nested Loop (cost=15,301.71..132,198.18 rows=51 width=629) (actual time=1,922.705..2,148.179 rows=822 loops=1)

9. 1.178 2,145.360 ↓ 16.1 822 1

Nested Loop (cost=15,301.43..132,182.79 rows=51 width=604) (actual time=1,922.692..2,145.360 rows=822 loops=1)

10. 126.072 2,140.072 ↓ 16.1 822 1

Merge Join (cost=15,301.01..132,154.98 rows=51 width=577) (actual time=1,922.664..2,140.072 rows=822 loops=1)

  • Merge Cond: (nq_transaction.transaction_id = nq_gl_account_entry.transaction_id)
11. 0.631 125.508 ↓ 6.2 822 1

Sort (cost=15,300.58..15,300.91 rows=132 width=545) (actual time=125.243..125.508 rows=822 loops=1)

  • Sort Key: nq_transaction.transaction_id
  • Sort Method: quicksort Memory: 93kB
12. 0.354 124.877 ↓ 6.2 822 1

Hash Join (cost=2,295.28..15,295.93 rows=132 width=545) (actual time=113.974..124.877 rows=822 loops=1)

  • Hash Cond: ((nq_transaction.modifiedby)::text = (s.user_name)::text)
13. 0.000 106.078 ↓ 6.1 822 1

Gather (cost=1,017.15..14,015.98 rows=134 width=553) (actual time=95.494..106.078 rows=822 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1.106 111.181 ↓ 4.9 274 3 / 3

Hash Join (cost=17.15..13,002.58 rows=56 width=553) (actual time=87.772..111.181 rows=274 loops=3)

  • Hash Cond: ((nq_transaction.transaction_type)::numeric = (nq_ref_transaction_type.transaction_code_id)::numeric)
15. 0.852 109.978 ↑ 1.4 1,013 3 / 3

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

16. 11.148 29.452 ↑ 4.0 632 3 / 3

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

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

18. 0.010 0.294 ↑ 1.2 13 3 / 3

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

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

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

  • Filter: (finance_company_id = 526)
  • Rows Removed by Filter: 346
20. 79.674 79.674 ↓ 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.116..0.126 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
21. 0.018 0.097 ↑ 1.3 6 3 / 3

Hash (cost=1.56..1.56 rows=8 width=520) (actual time=0.097..0.097 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.079 0.079 ↑ 1.3 6 3 / 3

Seq Scan on nq_ref_transaction_type (cost=0.00..1.56 rows=8 width=520) (actual time=0.066..0.079 rows=6 loops=3)

  • Filter: ((transaction_code_desc)::text = ANY ('{"Apply Chargeback","Apply Payment","Apply Premium Refund","New Negative Endorsement","Payout Contract","Reverse Payment","Premium Refund Reversal","Payout Reversal"}'::text[]))
  • Rows Removed by Filter: 22
23. 2.102 18.445 ↓ 1.0 10,134 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 540kB
24. 3.514 16.343 ↓ 1.0 10,134 1

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

  • Hash Cond: (nq_admin_user.user_id = s.contact_id)
25. 6.150 8.915 ↓ 1.0 10,138 1

HashAggregate (cost=445.99..547.13 rows=10,114 width=1,040) (actual time=6.742..8.915 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
26. 0.850 2.765 ↓ 1.0 10,139 1

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

27. 0.016 0.016 ↑ 1.0 44 1

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

28. 0.113 0.113 ↓ 1.0 530 1

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

29. 1.786 1.786 ↓ 1.0 9,565 1

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

30. 1.859 3.914 ↑ 1.0 10,138 1

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

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

  • Filter: (user_id <> 1)
  • Rows Removed by Filter: 1
32. 1,353.624 1,888.492 ↑ 1.0 1,470,724 1

GroupAggregate (cost=0.43..98,323.04 rows=1,482,415 width=36) (actual time=0.079..1,888.492 rows=1,470,724 loops=1)

  • Group Key: nq_gl_account_entry.transaction_id
33. 534.868 534.868 ↓ 1.1 1,823,870 1

Index Scan using nq_gl_account_entry_transactionid_fltr on nq_gl_account_entry (cost=0.43..62,430.52 rows=1,736,233 width=14) (actual time=0.065..534.868 rows=1,823,870 loops=1)

34. 4.110 4.110 ↑ 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.005..0.005 rows=1 loops=822)

  • Index Cond: (client_id = nq_base_contract.client_id)
35. 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)
36. 0.000 0.000 ↑ 1.0 1 822

Materialize (cost=0.14..8.17 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=822)

37. 0.010 0.010 ↑ 1.0 1 1

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

  • Index Cond: (finance_company_id = 526)
  • Heap Fetches: 1
38. 3.288 3.288 ↑ 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.004..0.004 rows=1 loops=822)

  • Index Cond: (nq_transaction.transaction_id = transaction_id)
39. 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)
40. 4.030 4.030 ↑ 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.005 rows=1 loops=806)

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