explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5RNB

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 7,681.563 ↑ 1.0 25 1

Limit (cost=82,424.90..84,696.90 rows=25 width=365) (actual time=7,649.741..7,681.563 rows=25 loops=1)

  • àáâãäåāăąèéêëēĕėęěìíîïĩīĭįòóôõöøōŏőùúûüũūŭůųÇçÑñÝýÿĆćĈĉĊċČčĎďĐđ
  • ĜĝĞğĠġĢģĤĥĦħ'::text, 'AAAAAAAAAEEEEEEEEEIIIIIIIIOOOOOOOOOUUUUUUUUUUaaaaaaaaa
  • eeeeeeeeeiiiiiiiiooooooooouuuuuuuuuCcNnYyyCcCcCcCcDdDd
  • GgGgGgGgHhHh'::text) ~~ '%FABRICIO%'::text)) OR (hashed SubPlan 15))
2. 0.552 7,681.547 ↑ 7,496.2 25 1

Result (cost=82,424.90..17,113,700.42 rows=187,404 width=365) (actual time=7,649.738..7,681.547 rows=25 loops=1)

3. 0.690 7,644.745 ↑ 7,496.2 25 1

Sort (cost=82,424.90..82,893.41 rows=187,404 width=109) (actual time=7,644.725..7,644.745 rows=25 loops=1)

  • Sort Key: tb_core_customer_data.cpf_cnpj
  • Sort Method: top-N heapsort Memory: 28kB
4. 113.434 7,644.055 ↑ 103.2 1,816 1

Hash Right Join (cost=55,372.97..77,136.49 rows=187,404 width=109) (actual time=7,443.980..7,644.055 rows=1,816 loops=1)

  • Hash Cond: ((tb_core_customer_external.id_contract = tb_core_customer_data.id_contract) AND (tb_core_customer_external.id_customer = tb_core_customer_data.id_customer))
5. 87.932 87.932 ↑ 1.0 374,748 1

Seq Scan on tb_core_customer_external (cost=0.00..8,374.48 rows=374,748 width=45) (actual time=0.011..87.932 rows=374,748 loops=1)

6. 1.683 7,442.689 ↑ 103.2 1,816 1

Hash (cost=50,913.91..50,913.91 rows=187,404 width=48) (actual time=7,442.689..7,442.689 rows=1,816 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 2128kB
7. 6,792.712 7,441.006 ↑ 103.2 1,816 1

Seq Scan on tb_core_customer_data (cost=13,142.56..50,913.91 rows=187,404 width=48) (actual time=791.313..7,441.006 rows=1,816 loops=1)

  • Filter: (((id_contract = 39) AND (translate(upper((name)::text), 'ÀÁÂÃÄÅĀĂĄÈÉÊËĒĔĖĘĚÌÍÎÏĨĪĮİÒÓÔÕÖØŌŎŐÙÚÛÜŨŪŬŮŰŲ
  • Rows Removed by Filter: 372955
8.          

SubPlan (for Seq Scan)

9. 648.294 648.294 ↓ 13.9 530 1

Seq Scan on tb_core_customer_emails (cost=0.00..13,142.47 rows=38 width=4) (actual time=194.574..648.294 rows=530 loops=1)

  • Filter: ((id_contract = 39) AND (upper((email)::text) ~~ '%FABRICIO%'::text))
  • Rows Removed by Filter: 376011
10.          

SubPlan (for Result)

11. 0.075 0.550 ↑ 1.0 1 25

Result (cost=10.21..10.22 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=25)

12.          

Initplan (for Result)

13. 0.025 0.475 ↑ 2.0 1 25

Result (cost=0.42..10.21 rows=2 width=8) (actual time=0.018..0.019 rows=1 loops=25)

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
14. 0.450 0.450 ↑ 2.0 1 25

Index Only Scan using tb_core_customer_broker_accounts_pk on tb_core_customer_broker_accounts customer_contas_ids (cost=0.42..10.21 rows=2 width=8) (actual time=0.017..0.018 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
  • Heap Fetches: 29
15. 0.050 0.175 ↑ 1.0 1 25

Result (cost=10.21..10.22 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=25)

16.          

Initplan (for Result)

17. 0.025 0.125 ↑ 2.0 1 25

Result (cost=0.42..10.21 rows=2 width=8) (actual time=0.005..0.005 rows=1 loops=25)

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
18. 0.100 0.100 ↑ 2.0 1 25

Index Only Scan using tb_core_customer_broker_accounts_pk on tb_core_customer_broker_accounts customer_contas_ids2 (cost=0.42..10.21 rows=2 width=8) (actual time=0.003..0.004 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
  • Heap Fetches: 29
19. 0.050 0.175 ↑ 1.0 1 25

Result (cost=10.21..10.22 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=25)

20.          

Initplan (for Result)

21. 0.025 0.125 ↑ 2.0 1 25

Result (cost=0.42..10.21 rows=2 width=2) (actual time=0.005..0.005 rows=1 loops=25)

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
22. 0.100 0.100 ↑ 2.0 1 25

Index Scan using tb_core_customer_broker_accounts_pk on tb_core_customer_broker_accounts customer_contas_ids1 (cost=0.42..10.21 rows=2 width=2) (actual time=0.003..0.004 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
23. 0.050 0.525 ↑ 1.0 1 25

Result (cost=26.85..26.86 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=25)

24.          

Initplan (for Result)

25. 0.025 0.475 ↑ 2.0 1 25

Result (cost=0.70..26.85 rows=2 width=32) (actual time=0.016..0.019 rows=1 loops=25)

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
26. 0.089 0.450 ↑ 2.0 1 25

Nested Loop (cost=0.70..26.85 rows=2 width=32) (actual time=0.015..0.018 rows=1 loops=25)

27. 0.100 0.100 ↑ 2.0 1 25

Index Scan using tb_core_customer_broker_accounts_pk on tb_core_customer_broker_accounts customer_contas (cost=0.42..10.21 rows=2 width=8) (actual time=0.003..0.004 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
28. 0.261 0.261 ↑ 1.0 1 29

Index Scan using tb_core_broker_office_pk on tb_core_broker_office (cost=0.28..8.30 rows=1 width=30) (actual time=0.008..0.009 rows=1 loops=29)

  • Index Cond: ((id_contract = 39) AND (id_office = customer_contas.id_office))
29. 0.075 13.650 ↑ 1.0 1 25

Result (cost=9.48..9.49 rows=1 width=32) (actual time=0.546..0.546 rows=1 loops=25)

30.          

Initplan (for Result)

31. 0.270 13.575 ↑ 1.0 1 25

Nested Loop (cost=0.42..9.48 rows=1 width=32) (actual time=0.541..0.543 rows=1 loops=25)

  • Join Filter: ((customer_emails.cd_contact_type)::text = (dm_contact_type.cd_contact_type)::text)
  • Rows Removed by Join Filter: 1
32. 12.525 12.525 ↑ 1.0 1 25

Index Scan using tb_core_customer_emails_pk on tb_core_customer_emails customer_emails (cost=0.42..8.44 rows=1 width=28) (actual time=0.501..0.501 rows=1 loops=25)

  • Index Cond: ((id_contract = tb_core_customer_data.id_contract) AND (id_customer = tb_core_customer_data.id_customer))
33. 0.780 0.780 ↓ 2.0 2 26

Seq Scan on dm_contact_type (cost=0.00..1.02 rows=1 width=32) (actual time=0.030..0.030 rows=2 loops=26)

  • Filter: (id_contract = tb_core_customer_data.id_contract)
34. 0.175 20.925 ↑ 1.0 1 25

Result (cost=14.26..14.27 rows=1 width=32) (actual time=0.836..0.837 rows=1 loops=25)

35.          

Initplan (for Result)

36. 0.325 20.750 ↓ 1.5 3 25

Nested Loop (cost=0.42..14.26 rows=2 width=32) (actual time=0.815..0.830 rows=3 loops=25)

  • Join Filter: ((customer_phones.cd_phone_type)::text = (dm_phone_type.cd_phone_type)::text)
  • Rows Removed by Join Filter: 5
37. 0.550 0.550 ↑ 1.0 3 25

Seq Scan on dm_phone_type (cost=0.00..1.03 rows=3 width=64) (actual time=0.022..0.022 rows=3 loops=25)

38. 0.150 19.875 ↓ 1.5 3 75

Materialize (cost=0.42..13.06 rows=2 width=52) (actual time=0.259..0.265 rows=3 loops=75)

39. 0.175 19.725 ↓ 1.5 3 25

Nested Loop (cost=0.42..13.05 rows=2 width=52) (actual time=0.774..0.789 rows=3 loops=25)

  • Join Filter: ((customer_phones.cd_contact_type)::text = (dm_contact_type_1.cd_contact_type)::text)
  • Rows Removed by Join Filter: 3
40. 0.050 0.050 ↓ 2.0 2 25

Seq Scan on dm_contact_type dm_contact_type_1 (cost=0.00..1.02 rows=1 width=64) (actual time=0.001..0.002 rows=2 loops=25)

  • Filter: (id_contract = tb_core_customer_data.id_contract)
41. 19.500 19.500 ↑ 1.0 3 50

Index Scan using tb_core_customer_phones_pk on tb_core_customer_phones customer_phones (cost=0.42..11.99 rows=3 width=24) (actual time=0.381..0.390 rows=3 loops=50)

  • Index Cond: ((id_contract = tb_core_customer_data.id_contract) AND (id_customer = tb_core_customer_data.id_customer))
42. 0.075 0.250 ↑ 1.0 1 25

Result (cost=9.49..9.50 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=25)

43.          

Initplan (for Result)

44. 0.025 0.175 ↓ 0.0 0 25

Nested Loop (cost=0.42..9.49 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=25)

45. 0.150 0.150 ↓ 0.0 0 25

Index Scan using tb_core_customer_emails_pk on tb_core_customer_emails customer_emails_1 (cost=0.42..8.44 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=25)

  • Index Cond: ((id_contract = tb_core_customer_data.id_contract) AND (id_customer = tb_core_customer_data.id_customer))
  • Filter: ((cd_contact_type)::text = 'PRIN'::text)
  • Rows Removed by Filter: 1
46. 0.000 0.000 ↓ 0.0 0

Seq Scan on dm_contact_type dm_contact_type_2 (cost=0.00..1.03 rows=1 width=32) (never executed)

  • Filter: (((cd_contact_type)::text = 'PRIN'::text) AND (id_contract = tb_core_customer_data.id_contract))
Planning time : 1.935 ms
Execution time : 7,681.915 ms