explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nqxw

Settings
# exclusive inclusive rows x rows loops node
1. 122.140 1,569.595 ↓ 2.0 116,010 1

Sort (cost=10,000,911,885.96..10,000,912,031.56 rows=58,237 width=201) (actual time=1,559.363..1,569.595 rows=116,010 loops=1)

  • Sort Key: proposta.prpdt_cadastro, proposta.prplocatario
  • Sort Method: quicksort Memory: 33872kB
2.          

CTE integracao

3. 0.002 19.528 ↓ 0.0 0 1

WindowAgg (cost=10,000,002,505.93..10,000,002,505.96 rows=1 width=56) (actual time=19.528..19.528 rows=0 loops=1)

4. 0.007 19.526 ↓ 0.0 0 1

Sort (cost=10,000,002,505.93..10,000,002,505.93 rows=1 width=56) (actual time=19.526..19.526 rows=0 loops=1)

  • Sort Key: (COALESCE(clientes.clino_cpf, clientes.clino_cgc)), topics_kafka_log.date_log DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.002 19.519 ↓ 0.0 0 1

Nested Loop Left Join (cost=10,000,000,000.57..10,000,002,505.92 rows=1 width=56) (actual time=19.519..19.519 rows=0 loops=1)

6. 0.000 19.517 ↓ 0.0 0 1

Nested Loop (cost=10,000,000,000.15..10,000,002,497.47 rows=1 width=44) (actual time=19.517..19.517 rows=0 loops=1)

7. 19.517 19.517 ↓ 0.0 0 1

Seq Scan on topics_kafka_customer_integration (cost=10,000,000,000.00..10,000,002,489.30 rows=1 width=8) (actual time=19.517..19.517 rows=0 loops=1)

  • Filter: ((operation = 'insert'::text) AND ((integrated)::text = 'y'::text))
  • Rows Removed by Filter: 121953
8. 0.000 0.000 ↓ 0.0 0

Index Scan using topics_kafka_log_pkey on topics_kafka_log (cost=0.15..8.17 rows=1 width=44) (never executed)

  • Index Cond: (log_kafka_id = topics_kafka_customer_integration.log_kafka_id)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using clientes_clioid_key on clientes (cost=0.42..8.44 rows=1 width=20) (never executed)

  • Index Cond: (clioid = topics_kafka_customer_integration.identification_customer)
10. 376.351 1,447.455 ↓ 2.0 116,010 1

Hash Left Join (cost=196.06..904,770.65 rows=58,237 width=201) (actual time=218.443..1,447.455 rows=116,010 loops=1)

  • Hash Cond: (proposta.prpno_cpf_cgc = (integracao.cpf_cgc)::double precision)
11. 27.737 925.710 ↓ 2.0 116,010 1

Hash Left Join (cost=196.02..640,003.84 rows=58,237 width=169) (actual time=0.930..925.710 rows=116,010 loops=1)

  • Hash Cond: (proposta.prppmsoid = proposta_motivo_situacao.pmsoid)
12. 43.236 897.960 ↓ 2.0 116,010 1

Hash Left Join (cost=183.47..639,771.66 rows=58,237 width=145) (actual time=0.907..897.960 rows=116,010 loops=1)

  • Hash Cond: (proposta.prpeqcoid = equipamento_classe.eqcoid)
13. 95.566 854.514 ↓ 2.0 116,010 1

Hash Left Join (cost=143.78..638,935.99 rows=58,237 width=123) (actual time=0.686..854.514 rows=116,010 loops=1)

  • Hash Cond: (proposta.prptpcoid = tipo_contrato.tpcoid)
14. 758.334 758.365 ↓ 2.0 116,010 1

Index Scan using proposta_dtcadastro_idx on proposta (cost=3.75..638,010.56 rows=58,237 width=121) (actual time=0.089..758.365 rows=116,010 loops=1)

  • Index Cond: ((prpdt_cadastro >= '2018-05-07 00:00:00-03'::timestamp with time zone) AND (prpdt_cadastro <= '2019-05-08 23:59:59-03'::timestamp with time zone))
  • Filter: ((prpdt_exclusao IS NULL) AND CASE WHEN (hashed SubPlan 4) THEN ((hashed SubPlan 5) AND (SubPlan 6)) ELSE (prpoid = prpoid) END)
  • Rows Removed by Filter: 2
15.          

SubPlan (forIndex Scan)

16. 0.000 0.031 ↑ 1.0 1 1

Limit (cost=0.56..1.74 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=1)

17. 0.001 0.031 ↑ 2,010.0 1 1

Nested Loop (cost=0.56..2,368.60 rows=2,010 width=4) (actual time=0.031..0.031 rows=1 loops=1)

18. 0.021 0.021 ↑ 2,010.0 1 1

Index Only Scan using usuario_permissao_uf_idx on usuario_permissao_uf (cost=0.28..62.43 rows=2,010 width=4) (actual time=0.021..0.021 rows=1 loops=1)

  • Heap Fetches: 0
19. 0.009 0.009 ↑ 1.0 1 1

Index Scan using usuarios_cd_usuario_key on usuarios (cost=0.29..1.14 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (cd_usuario = usuario_permissao_uf.upfusuoid)
  • Filter: ((usudepoid <> 79) OR (usudepoid <> 48))
20. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.83..1.58 rows=1 width=4) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=0.83..403.89 rows=542 width=4) (never executed)

  • Merge Cond: (endereco_representante.endvrepoid = representante.repoid)
22. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.56..188.12 rows=542 width=4) (never executed)

  • Join Filter: (endereco_representante.endvuf = usuario_permissao_uf_1.upfuf)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using endereco_representante_pkey on endereco_representante (cost=0.28..151.84 rows=1,865 width=7) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.28..8.31 rows=1 width=3) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.28..8.29 rows=1 width=3) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Index Scan using usuario_permissao_uf_idx on usuario_permissao_uf usuario_permissao_uf_1 (cost=0.28..8.29 rows=1 width=3) (never executed)

  • Index Cond: (upfusuoid = 10731)
27. 0.000 0.000 ↓ 0.0 0

Index Only Scan using representante_pkey on representante (cost=0.28..204.33 rows=1,865 width=4) (never executed)

  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.28..8.29 rows=1 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using tipo_contrato_pkey on tipo_contrato tipo_contrato_1 (cost=0.28..8.29 rows=1 width=4) (never executed)

  • Index Cond: (proposta.prptpcoid = tpcoid)
  • Filter: (tpcseguradora IS FALSE)
30. 0.162 0.583 ↑ 1.0 645 1

Hash (cost=131.97..131.97 rows=645 width=18) (actual time=0.583..0.583 rows=645 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
31. 0.421 0.421 ↑ 1.0 645 1

Index Scan using tipo_contrato_pkey on tipo_contrato (cost=0.28..131.97 rows=645 width=18) (actual time=0.006..0.421 rows=645 loops=1)

32. 0.056 0.210 ↑ 1.0 246 1

Hash (cost=36.62..36.62 rows=246 width=26) (actual time=0.210..0.210 rows=246 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
33. 0.154 0.154 ↑ 1.0 246 1

Index Scan using equipamento_classe_pkey on equipamento_classe (cost=0.14..36.62 rows=246 width=26) (actual time=0.006..0.154 rows=246 loops=1)

34. 0.007 0.013 ↑ 1.0 15 1

Hash (cost=12.36..12.36 rows=15 width=32) (actual time=0.013..0.013 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.006 0.006 ↑ 1.0 15 1

Index Scan using proposta_motivo_situacao_pkey on proposta_motivo_situacao (cost=0.14..12.36 rows=15 width=32) (actual time=0.003..0.006 rows=15 loops=1)

36. 0.001 19.530 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=40) (actual time=19.530..19.530 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
37. 19.529 19.529 ↓ 0.0 0 1

CTE Scan on integracao (cost=0.00..0.02 rows=1 width=40) (actual time=19.529..19.529 rows=0 loops=1)

  • Filter: (rank = 1)
38.          

SubPlan (forHash Left Join)

39. 0.000 0.000 ↓ 0.0 0

Append (cost=0.12..92.45 rows=21 width=0) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo_idx on proposta_anexo (cost=0.12..8.14 rows=1 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo0_idx on proposta_anexo0 (cost=0.29..8.42 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo1_idx on proposta_anexo1 (cost=0.29..8.46 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
43. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo2_idx on proposta_anexo2 (cost=0.29..8.41 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo3_idx on proposta_anexo3 (cost=0.29..8.43 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo4_idx on proposta_anexo4 (cost=0.29..8.43 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo5_idx on proposta_anexo5 (cost=0.29..8.43 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo6_idx on proposta_anexo6 (cost=0.29..8.44 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo7_idx on proposta_anexo7 (cost=0.29..8.46 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo8_idx on proposta_anexo8 (cost=0.29..8.43 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using proposta_anexo9_idx on proposta_anexo9 (cost=0.29..8.41 rows=2 width=0) (never executed)

  • Index Cond: (prpaprpoid = proposta.prpoid)
51. 16.213 125.864 ↑ 1.0 232,520 1

Append (cost=0.12..9,897.94 rows=232,521 width=4) (actual time=2.235..125.864 rows=232,520 loops=1)

52. 0.005 0.005 ↓ 0.0 0 1

Index Scan using proposta_anexo_idx on proposta_anexo proposta_anexo_1 (cost=0.12..8.14 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

53. 10.473 12.629 ↑ 1.0 22,881 1

Bitmap Heap Scan on proposta_anexo0 proposta_anexo0_1 (cost=380.41..972.22 rows=22,881 width=4) (actual time=2.227..12.629 rows=22,881 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=363
54. 2.156 2.156 ↑ 1.0 22,881 1

Bitmap Index Scan on proposta_anexo0_idx (cost=0.00..374.69 rows=22,881 width=0) (actual time=2.156..2.156 rows=22,881 loops=1)

55. 8.614 10.713 ↑ 1.0 23,653 1

Bitmap Heap Scan on proposta_anexo1 proposta_anexo1_1 (cost=396.47..1,008.00 rows=23,653 width=4) (actual time=2.163..10.713 rows=23,653 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=375
56. 2.099 2.099 ↑ 1.0 23,653 1

Bitmap Index Scan on proposta_anexo1_idx (cost=0.00..390.55 rows=23,653 width=0) (actual time=2.099..2.099 rows=23,653 loops=1)

57. 7.991 10.072 ↑ 1.0 22,845 1

Bitmap Heap Scan on proposta_anexo2 proposta_anexo2_1 (cost=380.22..970.67 rows=22,845 width=4) (actual time=2.145..10.072 rows=22,845 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=362
58. 2.081 2.081 ↑ 1.0 22,845 1

Bitmap Index Scan on proposta_anexo2_idx (cost=0.00..374.51 rows=22,845 width=0) (actual time=2.081..2.081 rows=22,845 loops=1)

59. 8.831 11.007 ↑ 1.0 23,458 1

Bitmap Heap Scan on proposta_anexo3 proposta_anexo3_1 (cost=391.44..998.02 rows=23,458 width=4) (actual time=2.237..11.007 rows=23,458 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=372
60. 2.176 2.176 ↑ 1.0 23,458 1

Bitmap Index Scan on proposta_anexo3_idx (cost=0.00..385.58 rows=23,458 width=0) (actual time=2.176..2.176 rows=23,458 loops=1)

61. 8.700 10.815 ↑ 1.0 23,479 1

Bitmap Heap Scan on proposta_anexo4 proposta_anexo4_1 (cost=395.55..1,002.34 rows=23,479 width=4) (actual time=2.175..10.815 rows=23,479 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=372
62. 2.115 2.115 ↑ 1.0 23,479 1

Bitmap Index Scan on proposta_anexo4_idx (cost=0.00..389.68 rows=23,479 width=0) (actual time=2.115..2.115 rows=23,479 loops=1)

63. 8.600 10.731 ↑ 1.0 23,811 1

Bitmap Heap Scan on proposta_anexo5 proposta_anexo5_1 (cost=397.30..1,012.41 rows=23,811 width=4) (actual time=2.190..10.731 rows=23,811 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=377
64. 2.131 2.131 ↑ 1.0 23,811 1

Bitmap Index Scan on proposta_anexo5_idx (cost=0.00..391.34 rows=23,811 width=0) (actual time=2.131..2.131 rows=23,811 loops=1)

65. 9.120 11.281 ↑ 1.0 23,295 1

Bitmap Heap Scan on proposta_anexo6 proposta_anexo6_1 (cost=390.59..992.54 rows=23,295 width=4) (actual time=2.226..11.281 rows=23,295 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=369
66. 2.161 2.161 ↑ 1.0 23,295 1

Bitmap Index Scan on proposta_anexo6_idx (cost=0.00..384.76 rows=23,295 width=0) (actual time=2.161..2.161 rows=23,295 loops=1)

67. 9.031 11.116 ↑ 1.0 22,972 1

Bitmap Heap Scan on proposta_anexo7 proposta_anexo7_1 (cost=384.89..978.61 rows=22,972 width=4) (actual time=2.148..11.116 rows=22,972 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=364
68. 2.085 2.085 ↑ 1.0 22,972 1

Bitmap Index Scan on proposta_anexo7_idx (cost=0.00..379.15 rows=22,972 width=0) (actual time=2.085..2.085 rows=22,972 loops=1)

69. 8.741 10.995 ↑ 1.0 23,137 1

Bitmap Heap Scan on proposta_anexo8 proposta_anexo8_1 (cost=385.76..984.13 rows=23,137 width=4) (actual time=2.319..10.995 rows=23,137 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=367
70. 2.254 2.254 ↑ 1.0 23,137 1

Bitmap Index Scan on proposta_anexo8_idx (cost=0.00..379.97 rows=23,137 width=0) (actual time=2.254..2.254 rows=23,137 loops=1)

71. 8.247 10.287 ↑ 1.0 22,989 1

Bitmap Heap Scan on proposta_anexo9 proposta_anexo9_1 (cost=376.98..970.87 rows=22,989 width=4) (actual time=2.104..10.287 rows=22,989 loops=1)

  • Recheck Cond: (prpaexclusao IS NULL)
  • Heap Blocks: exact=364
72. 2.040 2.040 ↑ 1.0 22,989 1

Bitmap Index Scan on proposta_anexo9_idx (cost=0.00..371.23 rows=22,989 width=0) (actual time=2.040..2.040 rows=22,989 loops=1)

Planning time : 3.236 ms
Execution time : 1,577.524 ms