explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gamf

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 39.877 ↑ 2,041.0 1 1

Sort (cost=26,664.48..26,669.58 rows=2,041 width=352) (actual time=39.877..39.877 rows=1 loops=1)

  • Sort Key: "*SELECT* 1".nomedependente
  • Sort Method: quicksort Memory: 25kB
2.          

CTE cte_contato

3. 0.001 0.014 ↓ 0.0 0 1

Bitmap Heap Scan on contato entity (cost=17.37..1,141.46 rows=6 width=8) (actual time=0.014..0.014 rows=0 loops=1)

  • Filter: ((tipo = ANY ('{E,S}'::bpchar[])) AND ((normalize(contato))::text ~~ '123'::text))
4. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on cidx_contato_contato (cost=0.00..17.37 rows=647 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((normalize(contato))::text = '123'::text)
5. 0.010 39.856 ↑ 2,041.0 1 1

HashAggregate (cost=25,390.41..25,410.82 rows=2,041 width=352) (actual time=39.851..39.856 rows=1 loops=1)

6. 0.000 39.846 ↑ 2,041.0 1 1

Append (cost=44.83..25,349.59 rows=2,041 width=352) (actual time=0.534..39.846 rows=1 loops=1)

7. 0.000 39.270 ↑ 758.0 1 1

Subquery Scan on *SELECT* 1 (cost=44.83..2,856.48 rows=758 width=352) (actual time=0.534..39.270 rows=1 loops=1)

8. 0.012 39.270 ↑ 758.0 1 1

Hash Join (cost=44.83..2,848.90 rows=758 width=352) (actual time=0.534..39.270 rows=1 loops=1)

  • Hash Cond: (public.cliente.crediarioid = public.crediario.id)
9. 0.000 38.875 ↑ 758.0 1 1

Nested Loop (cost=8.28..2,801.93 rows=758 width=194) (actual time=0.141..38.875 rows=1 loops=1)

10. 0.003 38.855 ↑ 758.0 1 1

Nested Loop (cost=8.28..2,235.37 rows=758 width=44) (actual time=0.121..38.855 rows=1 loops=1)

11. 30.704 38.825 ↑ 758.0 1 1

Hash Left Join (cost=8.28..1,679.39 rows=758 width=36) (actual time=0.092..38.825 rows=1 loops=1)

  • Hash Cond: (public.dependentecliente.id = cartaointerno.dependenteclienteid)
  • Filter: (((public.dependentecliente.numerocartao)::text = '123'::text) OR (((public.dependentecliente.codigo)::character varying)::text = '123'::text) OR ((cartaointerno.numero)::text = '123'::text))
12. 8.118 8.118 ↓ 1.0 64,591 1

Seq Scan on dependentecliente (cost=0.00..1,428.88 rows=64,588 width=94) (actual time=0.007..8.118 rows=64,591 loops=1)

13. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=8.27..8.27 rows=1 width=66) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
14. 0.003 0.003 ↓ 0.0 0 1

Index Scan using cidx_cartaointerno_numero on cartaointerno (cost=0.00..8.27 rows=1 width=66) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((numero)::text = '123'::text)
15. 0.027 0.027 ↑ 1.0 1 1

Index Scan using pk_cliente on cliente (cost=0.00..0.72 rows=1 width=24) (actual time=0.026..0.027 rows=1 loops=1)

  • Index Cond: (public.cliente.id = public.dependentecliente.clienteid)
16. 0.020 0.020 ↑ 1.0 1 1

Index Scan using pk_pessoa on pessoa pessoadependente (cost=0.00..0.73 rows=1 width=166) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (pessoadependente.id = public.dependentecliente.pessoaid)
17. 0.204 0.383 ↑ 1.0 780 1

Hash (cost=26.80..26.80 rows=780 width=174) (actual time=0.383..0.383 rows=780 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
18. 0.179 0.179 ↑ 1.0 780 1

Seq Scan on crediario (cost=0.00..26.80 rows=780 width=174) (actual time=0.006..0.179 rows=780 loops=1)

19. 0.001 0.547 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=11,948.10..20,770.84 rows=1,282 width=352) (actual time=0.547..0.547 rows=0 loops=1)

20. 0.025 0.546 ↓ 0.0 0 1

Hash Join (cost=11,948.10..20,758.02 rows=1,282 width=352) (actual time=0.546..0.546 rows=0 loops=1)

  • Hash Cond: (public.cliente.crediarioid = public.crediario.id)
21. 0.000 0.126 ↓ 0.0 0 1

Nested Loop (cost=11,911.55..20,703.84 rows=1,282 width=194) (actual time=0.126..0.126 rows=0 loops=1)

22. 0.002 0.126 ↓ 0.0 0 1

Merge Right Join (cost=11,911.55..19,763.52 rows=1,282 width=186) (actual time=0.126..0.126 rows=0 loops=1)

  • Merge Cond: (contato.pessoaid = pessoadependente.id)
23. 0.020 0.020 ↑ 129,444.0 1 1

Index Scan using idx_contato_pessoaid on contato (cost=0.00..7,517.92 rows=129,444 width=8) (actual time=0.020..0.020 rows=1 loops=1)

24. 0.009 0.104 ↓ 0.0 0 1

Sort (cost=11,911.55..11,914.76 rows=1,282 width=194) (actual time=0.104..0.104 rows=0 loops=1)

  • Sort Key: pessoadependente.id
  • Sort Method: quicksort Memory: 25kB
25. 0.015 0.095 ↓ 0.0 0 1

Hash Join (cost=8,717.26..11,845.37 rows=1,282 width=194) (actual time=0.095..0.095 rows=0 loops=1)

  • Hash Cond: (public.dependentecliente.pessoaid = pessoadependente.id)
26. 0.004 0.004 ↑ 64,588.0 1 1

Seq Scan on dependentecliente (cost=0.00..1,428.88 rows=64,588 width=36) (actual time=0.004..0.004 rows=1 loops=1)

27. 0.000 0.076 ↓ 0.0 0 1

Hash (cost=8,422.94..8,422.94 rows=8,186 width=166) (actual time=0.076..0.076 rows=0 loops=1)

  • Buckets: 1024 Batches: 2 Memory Usage: 0kB
28. 0.006 0.076 ↓ 0.0 0 1

Bitmap Heap Scan on pessoa pessoadependente (cost=227.45..8,422.94 rows=8,186 width=166) (actual time=0.076..0.076 rows=0 loops=1)

  • Recheck Cond: (((normalize(nome))::text ~~ '123'::text) OR ((cpf)::text = '123'::text) OR ((identidade)::text = '123'::text) OR ((cnpj)::text = '123'::text))
  • Filter: (((normalize(nome))::text ~~ '123'::text) OR ((cpf)::text = '123'::text) OR ((identidade)::text = '123'::text) OR ((cnpj)::text = '123'::text))
29. 0.002 0.070 ↓ 0.0 0 1

BitmapOr (cost=227.45..227.45 rows=8,248 width=0) (actual time=0.070..0.070 rows=0 loops=1)

30. 0.022 0.022 ↓ 0.0 0 1

Bitmap Index Scan on cidx_pessoa_nome (cost=0.00..64.05 rows=2,062 width=0) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((normalize(nome))::text = '123'::text)
31. 0.024 0.024 ↓ 0.0 0 1

Bitmap Index Scan on cidx_pessoa_cpf (cost=0.00..47.75 rows=2,062 width=0) (actual time=0.024..0.024 rows=0 loops=1)

  • Index Cond: ((cpf)::text = '123'::text)
32. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on cidx_pessoa_identidade (cost=0.00..47.74 rows=2,062 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: ((identidade)::text = '123'::text)
33. 0.009 0.009 ↓ 0.0 0 1

Bitmap Index Scan on cidx_pessoa_cnpj (cost=0.00..59.72 rows=2,062 width=0) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((cnpj)::text = '123'::text)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cliente on cliente (cost=0.00..0.72 rows=1 width=24) (never executed)

  • Index Cond: (public.cliente.id = public.dependentecliente.clienteid)
35. 0.245 0.395 ↑ 1.0 780 1

Hash (cost=26.80..26.80 rows=780 width=174) (actual time=0.395..0.395 rows=780 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
36. 0.150 0.150 ↑ 1.0 780 1

Seq Scan on crediario (cost=0.00..26.80 rows=780 width=174) (actual time=0.004..0.150 rows=780 loops=1)

37. 0.001 0.029 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=50.15..1,722.27 rows=1 width=352) (actual time=0.029..0.029 rows=0 loops=1)

38. 0.000 0.028 ↓ 0.0 0 1

Nested Loop (cost=50.15..1,722.26 rows=1 width=352) (actual time=0.028..0.028 rows=0 loops=1)

39. 0.001 0.028 ↓ 0.0 0 1

Nested Loop (cost=50.15..1,721.98 rows=1 width=194) (actual time=0.028..0.028 rows=0 loops=1)

40. 0.009 0.027 ↓ 0.0 0 1

Hash Join (cost=50.15..1,721.24 rows=1 width=186) (actual time=0.027..0.027 rows=0 loops=1)

  • Hash Cond: (public.dependentecliente.pessoaid = pessoadependente.id)
41. 0.002 0.002 ↑ 64,588.0 1 1

Seq Scan on dependentecliente (cost=0.00..1,428.88 rows=64,588 width=36) (actual time=0.002..0.002 rows=1 loops=1)

42. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=50.07..50.07 rows=6 width=174) (actual time=0.016..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
43. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.00..50.07 rows=6 width=174) (actual time=0.015..0.015 rows=0 loops=1)

44. 0.015 0.015 ↓ 0.0 0 1

CTE Scan on cte_contato (cost=0.00..0.12 rows=6 width=8) (actual time=0.015..0.015 rows=0 loops=1)

45. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_pessoa on pessoa pessoadependente (cost=0.00..8.31 rows=1 width=166) (never executed)

  • Index Cond: (pessoadependente.id = cte_contato.pessoaid)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cliente on cliente (cost=0.00..0.72 rows=1 width=24) (never executed)

  • Index Cond: (public.cliente.id = public.dependentecliente.clienteid)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_crediario on crediario (cost=0.00..0.27 rows=1 width=174) (never executed)

  • Index Cond: (public.crediario.id = public.cliente.crediarioid)