explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mhv

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 6,485.360 ↑ 1.0 25 1

Limit (cost=80,655.13..82,927.13 rows=25 width=365) (actual time=6,440.461..6,485.360 rows=25 loops=1)

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

Result (cost=80,655.13..17,246,523.93 rows=188,885 width=365) (actual time=6,440.456..6,485.344 rows=25 loops=1)

3. 2.794 6,434.121 ↑ 7,555.4 25 1

Sort (cost=80,655.13..81,127.35 rows=188,885 width=109) (actual time=6,434.102..6,434.121 rows=25 loops=1)

  • Sort Key: tb_core_customer_data.cpf_cnpj
  • Sort Method: top-N heapsort Memory: 28kB
4. 123.548 6,431.327 ↑ 19.9 9,500 1

Hash Right Join (cost=53,529.90..75,324.93 rows=188,885 width=109) (actual time=6,218.823..6,431.327 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. 89.641 89.641 ↑ 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.016..89.641 rows=374,748 loops=1)

6. 6.106 6,218.138 ↑ 19.9 9,500 1

Hash (cost=49,035.63..49,035.63 rows=188,885 width=48) (actual time=6,218.138..6,218.138 rows=9,500 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 2433kB
7. 5,673.105 6,212.032 ↑ 19.9 9,500 1

Seq Scan on tb_core_customer_data (cost=12,201.21..49,035.63 rows=188,885 width=48) (actual time=544.352..6,212.032 rows=9,500 loops=1)

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

SubPlan (for Seq Scan)

9. 538.927 538.927 ↓ 0.0 0 1

Seq Scan on tb_core_customer_emails (cost=0.00..12,201.11 rows=37 width=4) (actual time=538.927..538.927 rows=0 loops=1)

  • Filter: (((email)::text ~~* '%João%'::text) AND (id_contract = 39))
  • Rows Removed by Filter: 376541
10.          

SubPlan (for Result)

11. 0.075 0.600 ↑ 1.0 1 25

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

12.          

Initplan (for Result)

13. 0.025 0.525 ↑ 2.0 1 25

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

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

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
  • Heap Fetches: 27
15. 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)

16.          

Initplan (for Result)

17. 0.025 0.150 ↑ 2.0 1 25

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

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

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

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

20.          

Initplan (for Result)

21. 0.025 0.100 ↑ 2.0 1 25

Result (cost=0.42..10.21 rows=2 width=2) (actual time=0.004..0.004 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.003..0.003 rows=1 loops=25)

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

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

24.          

Initplan (for Result)

25. 0.025 0.450 ↑ 2.0 1 25

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

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

Nested Loop (cost=0.70..26.85 rows=2 width=32) (actual time=0.015..0.017 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.004..0.004 rows=1 loops=25)

  • Index Cond: ((id_contract = 39) AND (id_customer = tb_core_customer_data.id_customer))
28. 0.216 0.216 ↑ 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.008..0.008 rows=1 loops=27)

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

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

30.          

Initplan (for Result)

31. 0.178 11.500 ↑ 1.0 1 25

Nested Loop (cost=0.42..9.48 rows=1 width=32) (actual time=0.459..0.460 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. 11.250 11.250 ↑ 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.449..0.450 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.072 0.072 ↓ 2.0 2 24

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

  • Filter: (id_contract = tb_core_customer_data.id_contract)
34. 0.150 37.375 ↑ 1.0 1 25

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

35.          

Initplan (for Result)

36. 0.350 37.225 ↑ 1.0 2 25

Nested Loop (cost=0.42..14.26 rows=2 width=32) (actual time=1.474..1.489 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.050 0.050 ↑ 1.0 3 25

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

38. 0.125 36.825 ↑ 1.0 2 75

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

39. 0.150 36.700 ↑ 1.0 2 25

Nested Loop (cost=0.42..13.05 rows=2 width=52) (actual time=1.419..1.468 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.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. 36.500 36.500 ↑ 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.677..0.730 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.075 0.225 ↑ 1.0 1 25

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

43.          

Initplan (for Result)

44. 0.000 0.150 ↓ 0.0 0 25

Nested Loop (cost=0.42..9.49 rows=1 width=32) (actual time=0.006..0.006 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 : 3.023 ms
Execution time : 6,485.774 ms