explain.depesz.com

PostgreSQL's explain analyze made readable

Result: goxx

Settings
# exclusive inclusive rows x rows loops node
1. 4.127 948.077 ↓ 0.0 0 1

Limit (cost=240,447.95..240,454.28 rows=1 width=231) (actual time=948.077..948.077 rows=0 loops=1)

2. 218.369 943.950 ↓ 5.2 100,145 1

Hash Join (cost=119,502.03..240,447.95 rows=19,097 width=231) (actual time=187.375..943.950 rows=100,145 loops=1)

  • Hash Cond: (tb_dist_certificate_daily.id_instrument = tb_dist_fund_daily.id_instrument)
3. 28.467 323.723 ↓ 6.7 100,145 1

Nested Loop Left Join (cost=119,111.15..148,880.19 rows=14,895 width=191) (actual time=186.674..323.723 rows=100,145 loops=1)

  • Join Filter: ((tb_dist_certificate_daily.id_contract = tb_dist_certificate_quote_erosion_executed.id_contract) AND (tb_dist_certificate_daily.dt_reference = tb_dist_certificate_quote_erosion_executed.dt_reference) AND (tb_dist_certificate_daily.id_customer = tb_dist_certificate_quote_erosion_executed.id_customer) AND (tb_dist_certificate_daily.id_instrument = tb_dist_certificate_quote_erosion_executed.id_instrument) AND (tb_dist_certificate_daily.id_certificate = tb_dist_certificate_quote_erosion_executed.id_certificate))
4. 46.781 295.256 ↓ 6.7 100,145 1

Hash Left Join (cost=119,110.73..148,132.54 rows=14,895 width=193) (actual time=186.635..295.256 rows=100,145 loops=1)

  • Hash Cond: ((tb_dist_certificate.id_contract = tb_dist_fund_selected.id_contract) AND (tb_dist_certificate.id_instrument = tb_dist_fund_selected.id_instrument))
5. 0.000 247.914 ↓ 6.7 100,145 1

Gather (cost=119,026.59..147,970.01 rows=14,895 width=160) (actual time=186.054..247.914 rows=100,145 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 19.573 323.405 ↓ 5.4 33,382 3 / 3

Parallel Hash Left Join (cost=118,026.59..145,480.51 rows=6,206 width=160) (actual time=182.450..323.405 rows=33,382 loops=3)

  • Hash Cond: ((tb_dist_certificate.id_contract = tb_core_customer_data.id_contract) AND (tb_dist_certificate.id_customer = tb_core_customer_data.id_customer))
7. 44.553 224.911 ↓ 5.4 33,382 3 / 3

Parallel Hash Join (cost=113,223.97..140,645.32 rows=6,206 width=120) (actual time=103.082..224.911 rows=33,382 loops=3)

  • Hash Cond: ((tb_dist_certificate.id_customer = tb_dist_certificate_daily.id_customer) AND (tb_dist_certificate.id_instrument = tb_dist_certificate_daily.id_instrument) AND (tb_dist_certificate.id_certificate = tb_dist_certificate_daily.id_certificate))
8. 77.829 77.829 ↑ 1.3 83,182 3 / 3

Parallel Seq Scan on tb_dist_certificate (cost=0.00..26,599.46 rows=104,366 width=46) (actual time=0.027..77.829 rows=83,182 loops=3)

  • Filter: ((NOT is_from_minicom) AND (id_contract = 8))
  • Rows Removed by Filter: 362,352
9. 44.803 102.529 ↑ 1.3 77,644 3 / 3

Parallel Hash (cost=111,413.56..111,413.56 rows=103,452 width=82) (actual time=102.529..102.529 rows=77,644 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 29,664kB
10. 57.726 57.726 ↑ 1.3 77,644 3 / 3

Parallel Index Scan using tb_dist_certificate_daily_id_contract_dt_reference_idx on tb_dist_certificate_daily (cost=0.57..111,413.56 rows=103,452 width=82) (actual time=0.160..57.726 rows=77,644 loops=3)

  • Index Cond: ((id_contract = 8) AND (dt_reference = 20,200,603))
11. 12.055 78.921 ↑ 1.3 30,883 3 / 3

Parallel Hash (cost=4,223.55..4,223.55 rows=38,604 width=44) (actual time=78.921..78.921 rows=30,883 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 8,832kB
12. 66.866 66.866 ↑ 1.3 30,883 3 / 3

Parallel Seq Scan on tb_core_customer_data (cost=0.00..4,223.55 rows=38,604 width=44) (actual time=0.021..66.866 rows=30,883 loops=3)

  • Filter: (id_contract = 8)
13. 0.184 0.561 ↑ 1.0 987 1

Hash (cost=69.34..69.34 rows=987 width=45) (actual time=0.561..0.561 rows=987 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 84kB
14. 0.377 0.377 ↑ 1.0 987 1

Seq Scan on tb_dist_fund_selected (cost=0.00..69.34 rows=987 width=45) (actual time=0.008..0.377 rows=987 loops=1)

  • Filter: (id_contract = 8)
15. 0.000 0.000 ↓ 0.0 0 100,145

Materialize (cost=0.42..2.91 rows=2 width=26) (actual time=0.000..0.000 rows=0 loops=100,145)

16. 0.032 0.032 ↓ 0.0 0 1

Index Scan using tb_dist_certificate_quote_erosion_executed_idx1 on tb_dist_certificate_quote_erosion_executed (cost=0.42..2.90 rows=2 width=26) (actual time=0.032..0.032 rows=0 loops=1)

  • Index Cond: ((id_contract = 8) AND (dt_reference = 20,200,603))
17. 0.000 0.639 ↑ 1.0 755 1

Hash (cost=381.19..381.19 rows=775 width=27) (actual time=0.639..0.639 rows=755 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
  • -> Index Scan using tb_dist_fund_daily_dt_reference_id_contract_id_instrument_idx on tb_dist_fund_daily (cost=0.42..381.19 rows=775 width=27) (actual time=0.070rtificate))
18. 46.781 295.256 ↓ 6.7 100,145 1

Hash Left Join (cost=119,110.73..148,132.54 rows=14,895 width=193) (actual time=186.635..295.256 rows=100,145 loops=1)

  • Hash Cond: ((tb_dist_certificate.id_contract = tb_dist_fund_selected.id_contract) AND (tb_dist_certificate.id_instrument = tb_dist_fund_selected.id_instrument))
19. 0.000 247.914 ↓ 6.7 100,145 1

Gather (cost=119,026.59..147,970.01 rows=14,895 width=160) (actual time=186.054..247.914 rows=100,145 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 19.573 323.405 ↓ 5.4 33,382 3 / 3

Parallel Hash Left Join (cost=118,026.59..145,480.51 rows=6,206 width=160) (actual time=182.450..323.405 rows=33,382 loops=3)

  • Hash Cond: ((tb_dist_certificate.id_contract = tb_core_customer_data.id_contract) AND (tb_dist_certificate.id_customer = tb_core_customer_data.id_customer))
21. 44.553 224.911 ↓ 5.4 33,382 3 / 3

Parallel Hash Join (cost=113,223.97..140,645.32 rows=6,206 width=120) (actual time=103.082..224.911 rows=33,382 loops=3)

  • Hash Cond: ((tb_dist_certificate.id_customer = tb_dist_certificate_daily.id_customer) AND (tb_dist_certificate.id_instrument = tb_dist_certificate_daily.id_instrument) AND (tb_dist_certificate.id_certificate = tb_dist_certificate_daily.id_certificate))
22. 77.829 77.829 ↑ 1.3 83,182 3 / 3

Parallel Seq Scan on tb_dist_certificate (cost=0.00..26,599.46 rows=104,366 width=46) (actual time=0.027..77.829 rows=83,182 loops=3)

  • Filter: ((NOT is_from_minicom) AND (id_contract = 8))
  • Rows Removed by Filter: 362,352
23. 44.803 102.529 ↑ 1.3 77,644 3 / 3

Parallel Hash (cost=111,413.56..111,413.56 rows=103,452 width=82) (actual time=102.529..102.529 rows=77,644 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 29,664kB
24. 57.726 57.726 ↑ 1.3 77,644 3 / 3

Parallel Index Scan using tb_dist_certificate_daily_id_contract_dt_reference_idx on tb_dist_certificate_daily (cost=0.57..111,413.56 rows=103,452 width=82) (actual time=0.160..57.726 rows=77,644 loops=3)

  • Index Cond: ((id_contract = 8) AND (dt_reference = 20,200,603))
25. 12.055 78.921 ↑ 1.3 30,883 3 / 3

Parallel Hash (cost=4,223.55..4,223.55 rows=38,604 width=44) (actual time=78.921..78.921 rows=30,883 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 8,832kB
26. 66.866 66.866 ↑ 1.3 30,883 3 / 3

Parallel Seq Scan on tb_core_customer_data (cost=0.00..4,223.55 rows=38,604 width=44) (actual time=0.021..66.866 rows=30,883 loops=3)

  • Filter: (id_contract = 8)
27. 0.184 0.561 ↑ 1.0 987 1

Hash (cost=69.34..69.34 rows=987 width=45) (actual time=0.561..0.561 rows=987 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 84kB
28. 0.377 0.377 ↑ 1.0 987 1

Seq Scan on tb_dist_fund_selected (cost=0.00..69.34 rows=987 width=45) (actual time=0.008..0.377 rows=987 loops=1)

  • Filter: (id_contract = 8)
29. 0.000 0.000 ↓ 0.0 0 100,145

Materialize (cost=0.42..2.91 rows=2 width=26) (actual time=0.000..0.000 rows=0 loops=100,145)

30. 0.032 0.032 ↓ 0.0 0 1

Index Scan using tb_dist_certificate_quote_erosion_executed_idx1 on tb_dist_certificate_quote_erosion_executed (cost=0.42..2.90 rows=2 width=26) (actual time=0.032..0.032 rows=0 loops=1)

  • Index Cond: ((id_contract = 8) AND (dt_reference = 20,200,603))
31. 0.133 0.639 ↑ 1.0 755 1

Hash (cost=381.19..381.19 rows=775 width=27) (actual time=0.639..0.639 rows=755 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 54kB
32. 0.506 0.506 ↑ 1.0 755 1

Index Scan using tb_dist_fund_daily_dt_reference_id_contract_id_instrument_idx on tb_dist_fund_daily (cost=0.42..381.19 rows=775 width=27) (actual time=0.070..0.506 rows=755 loops=1)

  • Index Cond: ((dt_reference = 20,200,603) AND (id_contract = 8))
33.          

SubPlan (for Hash Join)

34. 300.435 400.580 ↑ 1.0 1 100,145

Nested Loop (cost=0.70..4.76 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=100,145)

  • -> Index Scan using tb_core_customer_broker_accounts_pk on tb_core_customer_broker_accounts (cost=0.42..2.46 rows=1 width=8) (actual time=0.002..0.003 rows=1loops=100,145)
  • Index Cond: ((id_contract = tb_core_customer_data.id_contract) AND (id_customer = tb_core_customer_data.id_customer))
  • Filter: (((tb_dist_certificate.cd_account)::character varying)::text = (((vl_account_number)::text || ''::text) || (vl_account_digit)::text))
  • Rows Removed by Filter: 0
35. 100.145 100.145 ↑ 1.0 1 100,145

Index Scan using tb_core_broker_office_pk on tb_core_broker_office (cost=0.28..2.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=100,145)

  • Index Cond: ((id_contract = tb_core_customer_data.id_contract) AND (id_office = tb_core_customer_broker_accounts.id_office))
Planning time : 35.737 ms
Execution time : 952.854 ms