explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VpQk

Settings
# exclusive inclusive rows x rows loops node
1. 4.766 2,414.822 ↓ 21.8 806 1

Sort (cost=131,950.26..131,950.35 rows=37 width=653) (actual time=2,414.777..2,414.822 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. 1.901 2,410.056 ↓ 21.8 806 1

Nested Loop (cost=14,914.82..131,949.30 rows=37 width=653) (actual time=2,238.930..2,410.056 rows=806 loops=1)

3. 0.014 0.014 ↑ 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.013..0.014 rows=1 loops=1)

  • Index Cond: (finance_company_id = 526)
4. 1.392 2,408.141 ↓ 21.8 806 1

Nested Loop Left Join (cost=14,914.67..131,940.67 rows=37 width=601) (actual time=2,238.901..2,408.141 rows=806 loops=1)

5. 0.738 2,403.525 ↓ 21.8 806 1

Nested Loop Left Join (cost=14,914.25..131,888.76 rows=37 width=629) (actual time=2,238.881..2,403.525 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.663 2,401.965 ↓ 17.1 822 1

Nested Loop Left Join (cost=14,914.11..131,880.99 rows=48 width=633) (actual time=2,238.869..2,401.965 rows=822 loops=1)

7. 0.724 2,398.836 ↓ 17.1 822 1

Nested Loop (cost=14,913.69..131,816.16 rows=48 width=629) (actual time=2,238.857..2,398.836 rows=822 loops=1)

8. 0.421 2,398.112 ↓ 17.1 822 1

Nested Loop (cost=14,913.54..131,807.40 rows=48 width=629) (actual time=2,238.846..2,398.112 rows=822 loops=1)

9. 0.592 2,396.047 ↓ 17.1 822 1

Nested Loop (cost=14,913.26..131,792.91 rows=48 width=604) (actual time=2,238.837..2,396.047 rows=822 loops=1)

10. 144.851 2,392.167 ↓ 17.1 822 1

Merge Join (cost=14,912.84..131,766.73 rows=48 width=577) (actual time=2,238.809..2,392.167 rows=822 loops=1)

  • Merge Cond: (nq_transaction.transaction_id = nq_gl_account_entry.transaction_id)
11. 0.719 156.973 ↓ 6.6 822 1

Sort (cost=14,912.41..14,912.72 rows=124 width=545) (actual time=156.791..156.973 rows=822 loops=1)

  • Sort Key: nq_transaction.transaction_id
  • Sort Method: quicksort Memory: 93kB
12. 0.555 156.254 ↓ 6.6 822 1

Hash Join (cost=2,294.87..14,908.10 rows=124 width=545) (actual time=149.073..156.254 rows=822 loops=1)

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

Gather (cost=1,016.74..13,628.26 rows=126 width=553) (actual time=119.647..126.313 rows=822 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1.280 137.130 ↓ 5.3 274 3 / 3

Hash Join (cost=16.74..12,615.66 rows=52 width=553) (actual time=110.781..137.130 rows=274 loops=3)

  • Hash Cond: ((nq_transaction.transaction_type)::numeric = (nq_ref_transaction_type.transaction_code_id)::numeric)
15. 1.300 135.755 ↑ 1.3 1,013 3 / 3

Nested Loop (cost=15.08..12,605.28 rows=1,312 width=41) (actual time=110.413..135.755 rows=1,013 loops=3)

16. 14.353 36.443 ↑ 3.7 632 3 / 3

Hash Join (cost=14.65..6,429.54 rows=2,355 width=21) (actual time=0.491..36.443 rows=632 loops=3)

  • Hash Cond: (nq_base_contract.finance_plan_id = nq_finance_plan.finance_plan_id)
17. 21.771 21.771 ↑ 1.2 52,016 3 / 3

Parallel Seq Scan on nq_base_contract (cost=0.00..6,242.20 rows=65,020 width=21) (actual time=0.017..21.771 rows=52,016 loops=3)

18. 0.012 0.319 ↑ 1.0 13 3 / 3

Hash (cost=14.49..14.49 rows=13 width=8) (actual time=0.319..0.319 rows=13 loops=3)

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

Seq Scan on nq_finance_plan (cost=0.00..14.49 rows=13 width=8) (actual time=0.111..0.307 rows=13 loops=3)

  • Filter: (finance_company_id = 526)
  • Rows Removed by Filter: 346
20. 98.012 98.012 ↓ 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.144..0.155 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.023 0.095 ↑ 1.3 6 3 / 3

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

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

Seq Scan on nq_ref_transaction_type (cost=0.00..1.56 rows=8 width=520) (actual time=0.056..0.072 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. 3.453 29.386 ↓ 1.0 10,134 1

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

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

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

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

HashAggregate (cost=445.99..547.13 rows=10,114 width=1,040) (actual time=10.705..14.161 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. 1.408 4.452 ↓ 1.0 10,139 1

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

27. 0.024 0.024 ↑ 1.0 44 1

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

28. 0.168 0.168 ↓ 1.0 530 1

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

29. 2.852 2.852 ↓ 1.0 9,565 1

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

30. 3.079 6.314 ↑ 1.0 10,138 1

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

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

  • Filter: (user_id <> 1)
  • Rows Removed by Filter: 1
32. 1,548.789 2,090.343 ↑ 1.0 1,470,724 1

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

  • Group Key: nq_gl_account_entry.transaction_id
33. 541.554 541.554 ↓ 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.027..541.554 rows=1,823,870 loops=1)

34. 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)
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.008 0.008 ↑ 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.007..0.008 rows=1 loops=1)

  • Index Cond: (finance_company_id = 526)
  • Heap Fetches: 1
38. 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)
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. 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.003..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 : 9.116 ms
Execution time : 2,415.752 ms