explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a3U1

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 7,831.806 ↑ 1.0 25 1

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

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

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

3. 0.745 7,757.992 ↑ 7,496.2 25 1

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

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

Hash Right Join (cost=55,372.97..77,136.49 rows=187,404 width=109) (actual time=7,546.277..7,757.247 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. 92.956 92.956 ↑ 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.020..92.956 rows=374,748 loops=1)

6. 2.145 7,543.803 ↑ 103.2 1,816 1

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

  • Buckets: 262,144 Batches: 2 Memory Usage: 2,128kB
7. 6,911.640 7,541.658 ↑ 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=798.088..7,541.658 rows=1,816 loops=1)

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

SubPlan (for Seq Scan)

9. 630.018 630.018 ↓ 13.9 530 1

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

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

SubPlan (for Result)

11. 0.075 0.800 ↑ 1.0 1 25

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

12.          

Initplan (for Result)

13. 0.025 0.725 ↑ 2.0 1 25

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

  • One-Time Filter: (tb_core_customer_data.id_contract = 39)
14. 0.700 0.700 ↑ 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.027..0.028 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.004..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.200 ↑ 1.0 1 25

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

20.          

Initplan (for Result)

21. 0.050 0.150 ↑ 2.0 1 25

Result (cost=0.42..10.21 rows=2 width=2) (actual time=0.005..0.006 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.004..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.017..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.016..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.009..0.009 rows=1 loops=29)

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

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

30.          

Initplan (for Result)

31. 0.217 24.975 ↑ 1.0 1 25

Nested Loop (cost=0.42..9.48 rows=1 width=32) (actual time=0.998..0.999 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. 23.900 23.900 ↑ 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.955..0.956 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.858 0.858 ↓ 2.0 2 26

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

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

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

35.          

Initplan (for Result)

36. 0.350 44.675 ↓ 1.5 3 25

Nested Loop (cost=0.42..14.26 rows=2 width=32) (actual time=1.772..1.787 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.600 0.600 ↑ 1.0 3 25

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

38. 0.150 43.725 ↓ 1.5 3 75

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

39. 0.175 43.575 ↓ 1.5 3 25

Nested Loop (cost=0.42..13.05 rows=2 width=52) (actual time=1.719..1.743 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. 43.350 43.350 ↑ 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.842..0.867 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.100 0.275 ↑ 1.0 1 25

Result (cost=9.49..9.50 rows=1 width=32) (actual time=0.011..0.011 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 : 25.653 ms
Execution time : 7,832.478 ms