explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vf25

Settings
# exclusive inclusive rows x rows loops node
1. 4.598 2,404.283 ↓ 21.8 806 1

Sort (cost=131,950.26..131,950.35 rows=37 width=653) (actual time=2,404.235..2,404.283 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.879 2,399.685 ↓ 21.8 806 1

Nested Loop (cost=14,914.82..131,949.30 rows=37 width=653) (actual time=2,228.378..2,399.685 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.280 2,397.792 ↓ 21.8 806 1

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

5. 0.724 2,393.288 ↓ 21.8 806 1

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

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

7. 0.697 2,388.589 ↓ 17.1 822 1

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

8. 0.409 2,387.892 ↓ 17.1 822 1

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

9. 1.311 2,385.839 ↓ 17.1 822 1

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

10. 139.885 2,382.062 ↓ 17.1 822 1

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

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

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

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
14. 1.287 139.692 ↓ 5.3 274 3 / 3

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

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

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

16. 14.670 37.497 ↑ 3.7 632 3 / 3

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

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

18. 0.012 0.340 ↑ 1.0 13 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.328 0.328 ↑ 1.0 13 3 / 3

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

  • Filter: (finance_company_id = 526)
  • Rows Removed by Filter: 346
20. 99.276 99.276 ↓ 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.146..0.157 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.020 0.092 ↑ 1.3 6 3 / 3

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

  • Buckets: 1,024 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.571 31.326 ↓ 1.0 10,134 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 540kB
24. 5.684 27.755 ↓ 1.0 10,134 1

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

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

HashAggregate (cost=445.99..547.13 rows=10,114 width=1,040) (actual time=11.779..15.325 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.414 4.669 ↓ 1.0 10,139 1

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

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

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

30. 3.289 6.746 ↑ 1.0 10,138 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 580kB
31. 3.457 3.457 ↑ 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.457 rows=10,138 loops=1)

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

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

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

34. 2.466 2.466 ↑ 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.003..0.003 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.012 0.012 ↑ 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.012..0.012 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 : 11.062 ms
Execution time : 2,405.431 ms