explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HGIv

Settings
# exclusive inclusive rows x rows loops node
1. 4.830 2,370.599 ↓ 20.7 806 1

Sort (cost=132,345.28..132,345.38 rows=39 width=653) (actual time=2,370.554..2,370.599 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.941 2,365.769 ↓ 20.7 806 1

Nested Loop (cost=15,299.74..132,344.25 rows=39 width=653) (actual time=2,193.103..2,365.769 rows=806 loops=1)

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

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

Nested Loop Left Join (cost=15,299.60..132,335.60 rows=39 width=601) (actual time=2,193.066..2,363.809 rows=806 loops=1)

5. 0.782 2,359.212 ↓ 20.7 806 1

Nested Loop Left Join (cost=15,299.17..132,280.88 rows=39 width=629) (actual time=2,193.026..2,359.212 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.688 2,357.608 ↓ 16.1 822 1

Nested Loop Left Join (cost=15,299.04..132,272.62 rows=51 width=633) (actual time=2,192.992..2,357.608 rows=822 loops=1)

7. 0.741 2,354.454 ↓ 16.1 822 1

Nested Loop (cost=15,298.61..132,203.74 rows=51 width=629) (actual time=2,192.962..2,354.454 rows=822 loops=1)

8. 0.409 2,353.713 ↓ 16.1 822 1

Nested Loop (cost=15,298.47..132,194.94 rows=51 width=629) (actual time=2,192.933..2,353.713 rows=822 loops=1)

9. 0.655 2,351.660 ↓ 16.1 822 1

Nested Loop (cost=15,298.18..132,179.55 rows=51 width=604) (actual time=2,192.908..2,351.660 rows=822 loops=1)

10. 139.471 2,347.717 ↓ 16.1 822 1

Merge Join (cost=15,297.76..132,151.73 rows=51 width=577) (actual time=2,192.853..2,347.717 rows=822 loops=1)

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

Sort (cost=15,297.34..15,297.67 rows=132 width=545) (actual time=154.762..154.984 rows=822 loops=1)

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

Hash Join (cost=2,295.28..15,292.69 rows=132 width=545) (actual time=147.712..154.253 rows=822 loops=1)

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

Gather (cost=1,017.15..14,012.74 rows=134 width=553) (actual time=118.618..124.681 rows=822 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1.238 136.332 ↓ 4.9 274 3 / 3

Hash Join (cost=17.15..12,999.34 rows=56 width=553) (actual time=109.783..136.332 rows=274 loops=3)

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

Nested Loop (cost=15.49..12,988.41 rows=1,394 width=41) (actual time=109.300..134.989 rows=1,013 loops=3)

16. 14.256 36.423 ↑ 4.0 632 3 / 3

Hash Join (cost=15.06..6,429.80 rows=2,501 width=21) (actual time=0.488..36.423 rows=632 loops=3)

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

18. 0.011 0.328 ↑ 1.2 13 3 / 3

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

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

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

  • Filter: (finance_company_id = 526)
  • Rows Removed by Filter: 346
20. 97.379 97.379 ↓ 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.142..0.154 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.019 0.105 ↑ 1.3 6 3 / 3

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

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

Seq Scan on nq_ref_transaction_type (cost=0.00..1.56 rows=8 width=520) (actual time=0.071..0.086 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.263 29.061 ↓ 1.0 10,134 1

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

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

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

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

HashAggregate (cost=445.99..547.13 rows=10,114 width=1,040) (actual time=10.696..14.164 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.411 4.457 ↓ 1.0 10,139 1

Append (cost=0.00..344.85 rows=10,114 width=1,040) (actual time=0.014..4.457 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.167 0.167 ↓ 1.0 530 1

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

29. 2.855 2.855 ↓ 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.855 rows=9,565 loops=1)

30. 3.010 6.225 ↑ 1.0 10,138 1

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

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

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

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

  • Group Key: nq_gl_account_entry.transaction_id
33. 535.289 535.289 ↓ 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.021..535.289 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.021 0.021 ↑ 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.020..0.021 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.833 ms
Execution time : 2,371.863 ms