explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M7hU

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 5,989.584 ↑ 1.0 25 1

Limit (cost=82,534.83..84,806.83 rows=25 width=365) (actual time=5,987.871..5,989.584 rows=25 loops=1)

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

Result (cost=82,534.83..17,248,403.63 rows=188,885 width=365) (actual time=5,987.866..5,989.572 rows=25 loops=1)

3. 2.741 5,987.632 ↑ 7,555.4 25 1

Sort (cost=82,534.83..83,007.04 rows=188,885 width=109) (actual time=5,987.625..5,987.632 rows=25 loops=1)

  • Sort Key: tb_core_customer_data.cpf_cnpj
  • Sort Method: top-N heapsort Memory: 28kB
4. 122.189 5,984.891 ↑ 19.9 9,500 1

Hash Right Join (cost=55,409.60..77,204.63 rows=188,885 width=109) (actual time=5,789.229..5,984.891 rows=9,500 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. 74.148 74.148 ↑ 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.012..74.148 rows=374,748 loops=1)

6. 5.762 5,788.554 ↑ 19.9 9,500 1

Hash (cost=50,915.32..50,915.32 rows=188,885 width=48) (actual time=5,788.554..5,788.554 rows=9,500 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 2433kB
7. 5,403.196 5,782.792 ↑ 19.9 9,500 1

Seq Scan on tb_core_customer_data (cost=13,143.97..50,915.32 rows=188,885 width=48) (actual time=380.634..5,782.792 rows=9,500 loops=1)

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

SubPlan (for Seq Scan)

9. 379.596 379.596 ↓ 0.0 0 1

Seq Scan on tb_core_customer_emails (cost=0.00..13,142.47 rows=602 width=4) (actual time=379.596..379.596 rows=0 loops=1)

  • Filter: ((id_contract = 39) AND (upper((email)::text) ~~ '%JOÃO%'::text))
  • Rows Removed by Filter: 376541
10.          

SubPlan (for Result)

11. 0.050 0.225 ↑ 1.0 1 25

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

12.          

Initplan (for Result)

13. 0.025 0.175 ↑ 2.0 1 25

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

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
14. 0.150 0.150 ↑ 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.006..0.006 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
  • Heap Fetches: 27
15. 0.025 0.100 ↑ 1.0 1 25

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

16.          

Initplan (for Result)

17. 0.000 0.075 ↑ 2.0 1 25

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

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
18. 0.075 0.075 ↑ 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.002..0.003 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
  • Heap Fetches: 27
19. 0.025 0.100 ↑ 1.0 1 25

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

20.          

Initplan (for Result)

21. 0.000 0.075 ↑ 2.0 1 25

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

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
22. 0.075 0.075 ↑ 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.002..0.003 rows=1 loops=25)

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

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

24.          

Initplan (for Result)

25. 0.025 0.200 ↑ 2.0 1 25

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

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

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

27. 0.050 0.050 ↑ 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.002..0.002 rows=1 loops=25)

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

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.003..0.003 rows=1 loops=27)

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

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

30.          

Initplan (for Result)

31. 0.076 0.225 ↑ 1.0 1 25

Nested Loop (cost=0.42..9.48 rows=1 width=32) (actual time=0.009..0.009 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. 0.125 0.125 ↑ 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.005..0.005 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.024 0.024 ↓ 2.0 2 24

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

  • Filter: (id_contract = tb_core_customer_data.id_contract)
34. 0.050 0.600 ↑ 1.0 1 25

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

35.          

Initplan (for Result)

36. 0.150 0.550 ↑ 1.0 2 25

Nested Loop (cost=0.42..14.26 rows=2 width=32) (actual time=0.015..0.022 rows=2 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.025 0.025 ↑ 1.0 3 25

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

38. 0.075 0.375 ↑ 1.0 2 75

Materialize (cost=0.42..13.06 rows=2 width=52) (actual time=0.004..0.005 rows=2 loops=75)

39. 0.075 0.300 ↑ 1.0 2 25

Nested Loop (cost=0.42..13.05 rows=2 width=52) (actual time=0.009..0.012 rows=2 loops=25)

  • Join Filter: ((customer_phones.cd_contact_type)::text = (dm_contact_type_1.cd_contact_type)::text)
  • Rows Removed by Join Filter: 2
40. 0.025 0.025 ↓ 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.001 rows=2 loops=25)

  • Filter: (id_contract = tb_core_customer_data.id_contract)
41. 0.200 0.200 ↑ 1.5 2 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.003..0.004 rows=2 loops=50)

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

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

43.          

Initplan (for Result)

44. 0.000 0.075 ↓ 0.0 0 25

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

45. 0.075 0.075 ↓ 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.003..0.003 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 : 2.538 ms
Execution time : 5,989.944 ms