explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3dZT

Settings
# exclusive inclusive rows x rows loops node
1. 7.357 13,413.514 ↓ 5,555.0 5,555 1

Sort (cost=27,076.75..27,076.75 rows=1 width=928) (actual time=13,413.141..13,413.514 rows=5,555 loops=1)

  • Sort Key: cn.nome_canal, cs.data_contrato
  • Sort Method: quicksort Memory: 3103kB
2. 5.818 13,406.157 ↓ 5,555.0 5,555 1

Unique (cost=27,076.59..27,076.73 rows=1 width=928) (actual time=13,399.899..13,406.157 rows=5,555 loops=1)

3. 16.814 13,400.339 ↓ 5,559.0 5,559 1

Sort (cost=27,076.59..27,076.60 rows=1 width=928) (actual time=13,399.898..13,400.339 rows=5,559 loops=1)

  • Sort Key: cs.contrato_servicos, ps.pessoa, ps.nome_completo, ed.codigo_postal, ed.endereco, ed.numero, ed.bairro, ed.complemento, ci.nome_completo, ((SubPlan 1)), re.sigla, ((SubPlan 2)), ps.data_nascimento, cs.data_contrato, ((((date_part('m (...)
  • Sort Method: quicksort Memory: 3105kB
4. 2,174.913 13,383.525 ↓ 5,559.0 5,559 1

Nested Loop Left Join (cost=1,014.40..27,076.58 rows=1 width=928) (actual time=9.974..13,383.525 rows=5,559 loops=1)

  • Join Filter: ((g.contratante_id = cs.contratante_id) AND (g.contrato_servicos2 = cs.contrato_servicos))
  • Rows Removed by Join Filter: 19106270
5. 5.453 119.559 ↓ 5,559.0 5,559 1

Nested Loop Left Join (cost=1,014.40..26,195.72 rows=1 width=354) (actual time=6.356..119.559 rows=5,559 loops=1)

6. 7.028 102.988 ↓ 5,559.0 5,559 1

Nested Loop Left Join (cost=1,014.27..26,195.56 rows=1 width=359) (actual time=6.351..102.988 rows=5,559 loops=1)

7. 8.990 79.283 ↓ 5,559.0 5,559 1

Nested Loop Left Join (cost=1,013.98..26,195.26 rows=1 width=347) (actual time=6.344..79.283 rows=5,559 loops=1)

8. 0.000 31.555 ↓ 5,534.0 5,534 1

Gather (cost=1,013.42..26,194.49 rows=1 width=296) (actual time=6.309..31.555 rows=5,534 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 2.958 117.338 ↓ 1,845.0 1,845 3 / 3

Hash Join (cost=13.42..25,194.39 rows=1 width=296) (actual time=2.972..117.338 rows=1,845 loops=3)

  • Hash Cond: (pc.contrato_id = cn.id)
10. 2.289 113.405 ↑ 1.2 1,845 3 / 3

Nested Loop Left Join (cost=4.44..25,177.08 rows=2,217 width=260) (actual time=1.981..113.405 rows=1,845 loops=3)

11. 2.907 107.427 ↑ 1.2 1,845 3 / 3

Nested Loop (cost=4.00..23,681.33 rows=2,217 width=266) (actual time=1.929..107.427 rows=1,845 loops=3)

12. 5.972 89.763 ↑ 1.2 1,845 3 / 3

Nested Loop (cost=3.57..18,121.80 rows=2,217 width=200) (actual time=1.839..89.763 rows=1,845 loops=3)

13. 2.943 78.257 ↑ 1.2 1,845 3 / 3

Hash Join (cost=3.28..16,779.75 rows=2,217 width=158) (actual time=1.789..78.257 rows=1,845 loops=3)

  • Hash Cond: (cs.plano_pagamento_id = pp.id)
14. 2.690 75.259 ↑ 1.2 1,845 3 / 3

Nested Loop Left Join (cost=1.27..16,771.38 rows=2,217 width=148) (actual time=1.526..75.259 rows=1,845 loops=3)

15. 1.726 68.880 ↑ 1.2 1,845 3 / 3

Nested Loop Left Join (cost=0.84..14,353.89 rows=2,217 width=140) (actual time=1.472..68.880 rows=1,845 loops=3)

16. 7.085 63.465 ↑ 1.2 1,845 3 / 3

Nested Loop (cost=0.42..12,546.26 rows=2,217 width=132) (actual time=1.423..63.465 rows=1,845 loops=3)

17. 39.778 39.778 ↑ 1.2 1,845 3 / 3

Parallel Seq Scan on contratos_servicos cs (cost=0.00..9,295.79 rows=2,222 width=124) (actual time=1.377..39.778 rows=1,845 loops=3)

  • Filter: ((data_contrato IS NOT NULL) AND (data_contrato >= '2020-01-01'::date) AND (tipo_contrato_id = 46128))
  • Rows Removed by Filter: 59101
18. 16.602 16.602 ↑ 1.0 1 5,534 / 3

Index Scan using pk_pesscont on pessoas_contratos pc (cost=0.42..1.46 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=5,534)

  • Index Cond: (id = cs.agente_venda_id)
19. 3.689 3.689 ↓ 0.0 0 5,534 / 3

Index Scan using pk_modpag on modos_pagamentos mp (cost=0.42..0.82 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=5,534)

  • Index Cond: (cs.modo_pagamento_id = id)
20. 3.689 3.689 ↓ 0.0 0 5,534 / 3

Index Scan using pk_pedi on pedidos pd (cost=0.43..1.09 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=5,534)

  • Index Cond: (mp.pedido_id = id)
21. 0.020 0.055 ↓ 1.0 46 3 / 3

Hash (cost=1.45..1.45 rows=45 width=26) (actual time=0.055..0.055 rows=46 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 0.035 0.035 ↓ 1.0 46 3 / 3

Seq Scan on planos_pagamentos pp (cost=0.00..1.45 rows=45 width=26) (actual time=0.025..0.035 rows=46 loops=3)

23. 5.534 5.534 ↑ 1.0 1 5,534 / 3

Index Scan using pk_produto on produtos prd (cost=0.29..0.61 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=5,534)

  • Index Cond: (id = cs.produto_relacionado_id)
24. 14.757 14.757 ↑ 1.0 1 5,534 / 3

Index Scan using pk_pess on pessoas ps (cost=0.43..2.51 rows=1 width=66) (actual time=0.008..0.008 rows=1 loops=5,534)

  • Index Cond: (id = cs.contratante_id)
25. 3.689 3.689 ↓ 0.0 0 5,534 / 3

Index Scan using pk_cont on contatos cc (cost=0.43..0.67 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=5,534)

  • Index Cond: (pd.contato_crm_id = id)
26. 0.023 0.975 ↓ 57.0 57 3 / 3

Hash (cost=8.97..8.97 rows=1 width=44) (actual time=0.975..0.975 rows=57 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.013 0.952 ↓ 57.0 57 3 / 3

Subquery Scan on cn (cost=8.96..8.97 rows=1 width=44) (actual time=0.935..0.952 rows=57 loops=3)

28. 0.256 0.939 ↓ 57.0 57 3 / 3

Sort (cost=8.96..8.96 rows=1 width=59) (actual time=0.934..0.939 rows=57 loops=3)

  • Sort Key: contratos_servicos.titulo_contrato, (split_part(contratos_servicos.observacoes_contrato, '|'::text, 2))
  • Sort Method: quicksort Memory: 31kB
29. 0.437 0.683 ↓ 57.0 57 3 / 3

Bitmap Heap Scan on contratos_servicos (cost=7.83..8.95 rows=1 width=59) (actual time=0.428..0.683 rows=57 loops=3)

  • Recheck Cond: ((titulo_contrato IS NOT NULL) AND (tipo_contrato_id = ANY ('{2094509,1643487}'::bigint[])))
  • Filter: (observacoes_contrato IS NOT NULL)
  • Rows Removed by Filter: 20
  • Heap Blocks: exact=50
30. 0.016 0.246 ↓ 0.0 0 3 / 3

BitmapAnd (cost=7.83..7.83 rows=1 width=0) (actual time=0.246..0.246 rows=0 loops=3)

31. 0.121 0.121 ↑ 1.1 156 3 / 3

Bitmap Index Scan on uk_contserv_titulo_contrato (cost=0.00..2.75 rows=164 width=0) (actual time=0.121..0.121 rows=156 loops=3)

  • Index Cond: (titulo_contrato IS NOT NULL)
32. 0.109 0.109 ↑ 2.5 95 3 / 3

Bitmap Index Scan on fk_contrserv_tipo_contrato_i (cost=0.00..4.83 rows=237 width=0) (actual time=0.109..0.109 rows=95 loops=3)

  • Index Cond: (tipo_contrato_id = ANY ('{2094509,1643487}'::bigint[]))
33. 38.738 38.738 ↑ 5.0 1 5,534

Index Scan using ak_ende_indice_pessoa_tipo_endereco_sequencia on enderecos ed (cost=0.56..0.72 rows=5 width=59) (actual time=0.007..0.007 rows=1 loops=5,534)

  • Index Cond: ((ps.id = pessoa_id) AND (tipo_endereco_id = 577))
34. 16.677 16.677 ↑ 1.0 1 5,559

Index Scan using pk_cida on cidades ci (cost=0.28..0.30 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=5,559)

  • Index Cond: (ed.cidade_id = id)
35. 11.118 11.118 ↑ 1.0 1 5,559

Index Scan using pk_regi on regioes re (cost=0.14..0.16 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=5,559)

  • Index Cond: (ci.regiao_id = id)
36. 2,051.271 2,051.271 ↑ 1.0 3,437 5,559

Seq Scan on z_troca_plano_tmp g (cost=0.00..67.37 rows=3,437 width=16) (actual time=0.003..0.369 rows=3,437 loops=5,559)

37.          

SubPlan (for Nested Loop Left Join)

38. 5.559 27.795 ↓ 0.0 0 5,559

Limit (cost=0.56..2.78 rows=1 width=13) (actual time=0.004..0.005 rows=0 loops=5,559)

39. 22.236 22.236 ↓ 0.0 0 5,559

Index Scan using ak_ende_indice_pessoa_tipo_endereco_sequencia on enderecos ff (cost=0.56..2.78 rows=1 width=13) (actual time=0.004..0.004 rows=0 loops=5,559)

  • Index Cond: ((pessoa_id = cs.contratante_id) AND (tipo_endereco_id = '566'::bigint))
40. 5.559 33.354 ↑ 1.0 1 5,559

Limit (cost=0.43..2.65 rows=1 width=14) (actual time=0.005..0.006 rows=1 loops=5,559)

41. 27.795 27.795 ↑ 1.0 1 5,559

Index Scan using fk_dope_pess_i on documentos_pessoas (cost=0.43..2.65 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=5,559)

  • Index Cond: (pessoa_id = ps.id)
  • Filter: (tipo_documento_id = 580)
  • Rows Removed by Filter: 0
42. 11.118 27.795 ↓ 0.0 0 5,559

Limit (cost=0.00..1.29 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=5,559)

43. 16.677 16.677 ↓ 0.0 0 5,559

Seq Scan on motivos_cancelamentos_contratos_servicos mc (cost=0.00..1.29 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=5,559)

  • Filter: (id = cs.motivo_cancelamento_contrato_id)
  • Rows Removed by Filter: 22
44. 5.559 27.795 ↑ 1.0 1 5,559

Limit (cost=0.42..2.64 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=5,559)

45. 22.236 22.236 ↑ 1.0 1 5,559

Index Scan using pk_contrserv on contratos_servicos pc6 (cost=0.42..2.64 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=5,559)

  • Index Cond: (id = cs.contratos_parceiro_id)
46. 21.084 21.084 ↑ 1.0 1 5,271

Index Scan using pk_contrserv on contratos_servicos contratos_servicos_1 (cost=0.42..2.64 rows=1 width=15) (actual time=0.003..0.004 rows=1 loops=5,271)

  • Index Cond: (id = pc.contrato_id)
47. 5.559 27.795 ↑ 1.0 1 5,559

Limit (cost=0.43..2.65 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=5,559)

48. 22.236 22.236 ↑ 1.0 1 5,559

Index Scan using pk_pess on pessoas ps_1 (cost=0.43..2.65 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=5,559)

  • Index Cond: (id = pc.pessoa_id)
49. 12.375 8,004.960 ↓ 0.0 0 5,559

Nested Loop (cost=0.57..606.19 rows=1 width=0) (actual time=1.440..1.440 rows=0 loops=5,559)

50. 16.677 16.677 ↑ 1.0 1 5,559

Index Scan using fk_pessoa_i on usuarios (cost=0.28..2.50 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=5,559)

  • Index Cond: (pessoa_id = pc.pessoa_id)
51. 7,975.908 7,975.908 ↓ 0.0 0 5,562

Index Only Scan using pk_adrc_oprd on aderencia_operadores (cost=0.29..603.68 rows=1 width=9) (actual time=1.434..1.434 rows=0 loops=5,562)

  • Index Cond: (login = (usuarios.login)::text)
  • Filter: ((date_part('month'::text, (dia)::timestamp without time zone) = date_part('month'::text, (CURRENT_DATE)::timestamp without time zone)) AND (date_part('year'::text, (dia)::timestamp without time zone) = date_part(' (...)
  • Rows Removed by Filter: 41
  • Heap Fetches: 230271
52. 5.559 155.652 ↓ 0.0 0 5,559

Limit (cost=15.61..15.62 rows=1 width=13) (actual time=0.028..0.028 rows=0 loops=5,559)

53. 11.118 150.093 ↓ 0.0 0 5,559

Sort (cost=15.61..15.62 rows=1 width=13) (actual time=0.027..0.027 rows=0 loops=5,559)

  • Sort Key: (COALESCE(turnos_operadores.data_termino, '2019-01-01'::date)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
54. 138.975 138.975 ↓ 0.0 0 5,559

Index Scan using pk_turn_oper on turnos_operadores (cost=0.28..15.60 rows=1 width=13) (actual time=0.023..0.025 rows=0 loops=5,559)

  • Index Cond: (pessoa_id = pc.pessoa_id)
55. 5.559 38.913 ↑ 1.0 1 5,559

Aggregate (cost=3.18..3.19 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=5,559)

56. 33.354 33.354 ↑ 3.0 1 5,559

Index Scan using fk_pesscont_contrserv_contrato_i on pessoas_contratos pc_1 (cost=0.42..3.17 rows=3 width=8) (actual time=0.005..0.006 rows=1 loops=5,559)

  • Index Cond: (contrato_id = cs.id)
57. 5.559 150.093 ↑ 1.0 1 5,559

Aggregate (cost=27.99..28.00 rows=1 width=8) (actual time=0.027..0.027 rows=1 loops=5,559)

58. 144.534 144.534 ↓ 0.0 0 5,559

Index Scan using fk_tito_contrserv_contrato_i on titulos tt (cost=0.43..27.97 rows=6 width=8) (actual time=0.024..0.026 rows=0 loops=5,559)

  • Index Cond: (contrato_id = cs.id)
  • Filter: (((situacao)::text = ANY ('{PM,L}'::text[])) AND ((tipo_operacao)::text = 'R'::text))
  • Rows Removed by Filter: 12
59. 5.559 66.708 ↑ 1.0 1 5,559

Aggregate (cost=27.99..28.00 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=5,559)

60. 61.149 61.149 ↓ 0.0 0 5,559

Index Scan using fk_tito_contrserv_contrato_i on titulos tt_1 (cost=0.43..27.97 rows=6 width=6) (actual time=0.010..0.011 rows=0 loops=5,559)

  • Index Cond: (contrato_id = cs.id)
  • Filter: (((situacao)::text = ANY ('{PM,L}'::text[])) AND ((tipo_operacao)::text = 'R'::text))
  • Rows Removed by Filter: 12
61. 5.559 61.149 ↑ 1.0 1 5,559

Aggregate (cost=27.98..27.99 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=5,559)

62. 55.590 55.590 ↓ 0.0 0 5,559

Index Scan using fk_tito_contrserv_contrato_i on titulos tt_2 (cost=0.43..27.97 rows=4 width=8) (actual time=0.010..0.010 rows=0 loops=5,559)

  • Index Cond: (contrato_id = cs.id)
  • Filter: (((tipo_operacao)::text = 'R'::text) AND ((situacao)::text = 'A'::text))
  • Rows Removed by Filter: 12
63. 5.559 61.149 ↑ 1.0 1 5,559

Aggregate (cost=27.98..27.99 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=5,559)

64. 55.590 55.590 ↓ 0.0 0 5,559

Index Scan using fk_tito_contrserv_contrato_i on titulos tt_3 (cost=0.43..27.97 rows=4 width=6) (actual time=0.010..0.010 rows=0 loops=5,559)

  • Index Cond: (contrato_id = cs.id)
  • Filter: (((tipo_operacao)::text = 'R'::text) AND ((situacao)::text = 'A'::text))
  • Rows Removed by Filter: 12
65. 0.000 0.000 ↓ 0.0 0 5,559

Index Scan using pk_pess on pessoas sub (cost=0.43..2.65 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=5,559)

  • Index Cond: (id = pd.vendedor_id)
66. 5.559 50.031 ↑ 1.0 1 5,559

Limit (cost=3.61..3.61 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=5,559)

67. 16.677 44.472 ↑ 2.0 1 5,559

Sort (cost=3.61..3.61 rows=2 width=8) (actual time=0.008..0.008 rows=1 loops=5,559)

  • Sort Key: contratos_servicos_2.data_contrato
  • Sort Method: quicksort Memory: 25kB
68. 27.795 27.795 ↑ 2.0 1 5,559

Index Scan using fk_contra_pess_contratante_i on contratos_servicos contratos_servicos_2 (cost=0.42..3.60 rows=2 width=8) (actual time=0.005..0.005 rows=1 loops=5,559)

  • Index Cond: (contratante_id = cs.contratante_id)
  • Filter: (tipo_contrato_id = 46128)
  • Rows Removed by Filter: 0
69. 5.559 5.559 ↓ 0.0 0 5,559

Index Scan using fk_pedi_modpag_modo_pagamento_i on pedidos (cost=0.43..2.65 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=5,559)

  • Index Cond: (modo_pagamento_id = cs.modo_pagamento_id)
70. 5.559 277.950 ↑ 1.0 1 5,559

Limit (cost=0.44..1.15 rows=1 width=32) (actual time=0.050..0.050 rows=1 loops=5,559)

71. 272.391 272.391 ↑ 6.0 1 5,559

Index Scan using fk_ende_pess_pessoa_i on enderecos ed_1 (cost=0.44..4.73 rows=6 width=32) (actual time=0.049..0.049 rows=1 loops=5,559)

  • Index Cond: (pessoa_id = ps.id)
  • Filter: ((regexp_replace(endereco, '[^0-9]'::text, ''::text, 'gi'::text) IS NOT NULL) AND (tipo_endereco_id = ANY ('{565,573,574,12190362}'::bigint[])) AND CASE WHEN ("substring"(regexp_replace(endereco, '[^0-9]'::text, '' (...)
  • Rows Removed by Filter: 2
Planning time : 22.910 ms
Execution time : 13,414.736 ms