explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YHAm : 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 36,397.130 ↓ 20.0 20 1

Limit (cost=90,620.43..90,620.45 rows=1 width=1,080) (actual time=36,397.089..36,397.130 rows=20 loops=1)

  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
2.          

CTE resolve_tipo_setor_filtro

3. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=52) (never executed)

4.          

CTE setores_base

5. 0.000 0.000 ↓ 0.0 0

Result (cost=8.72..10.76 rows=1 width=16) (never executed)

  • One-Time Filter: (NULLIF(NULL::text, ''::text) IS NOT NULL)
6.          

Initplan (for Result)

7. 0.000 0.000 ↓ 0.0 0

CTE Scan on resolve_tipo_setor_filtro (cost=0.00..0.02 rows=1 width=16) (never executed)

8. 0.000 0.000 ↓ 0.0 0

CTE Scan on resolve_tipo_setor_filtro resolve_tipo_setor_filtro_1 (cost=0.00..0.02 rows=1 width=32) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on setor (cost=2.63..4.67 rows=1 width=16) (never executed)

  • Recheck Cond: ((idsetor = $3) OR ((slug)::text = $4))
  • Filter: ((idsetor = $3) OR (((slug)::text = $4) AND (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid)))
10.          

Initplan (for Bitmap Heap Scan)

11. 0.000 0.000 ↓ 0.0 0

CTE Scan on resolve_tipo_setor_filtro resolve_tipo_setor_filtro_2 (cost=0.00..0.02 rows=1 width=16) (never executed)

12. 0.000 0.000 ↓ 0.0 0

CTE Scan on resolve_tipo_setor_filtro resolve_tipo_setor_filtro_3 (cost=0.00..0.02 rows=1 width=32) (never executed)

13. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=2.59..2.59 rows=2 width=0) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on setor_pkey (cost=0.00..1.30 rows=1 width=0) (never executed)

  • Index Cond: (idsetor = $3)
15. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on setor_slug_idx (cost=0.00..1.30 rows=1 width=0) (never executed)

  • Index Cond: ((slug)::text = $4)
16. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on setor setor_1 (cost=8.72..10.76 rows=1 width=16) (never executed)

  • Recheck Cond: ((idsetor = $1) OR ((slug)::text = $2) OR ((idsetorpai = $5) AND (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid)))
  • Filter: (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid)
17. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=4.01..4.01 rows=2 width=0) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on setor_pkey (cost=0.00..1.30 rows=1 width=0) (never executed)

  • Index Cond: (idsetor = $1)
19. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on setor_slug_idx (cost=0.00..1.30 rows=1 width=0) (never executed)

  • Index Cond: ((slug)::text = $2)
20. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on setor_idsetorpai_fornec_idx (cost=0.00..1.42 rows=1 width=0) (never executed)

  • Index Cond: ((idsetorpai = $5) AND (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid))
21.          

CTE select_setores

22. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=0.33..397.65 rows=41 width=32) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Result (cost=0.33..29.29 rows=21 width=32) (never executed)

  • One-Time Filter: (NULLIF(NULL::text, ''::text) IS NOT NULL)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using setor_fornec_idx on setor s (cost=0.33..29.29 rows=21 width=32) (never executed)

  • Index Cond: (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid)
  • Filter: ((hashed SubPlan 8) OR (hashed SubPlan 9))
25.          

SubPlan (for Index Scan)

26. 0.000 0.000 ↓ 0.0 0

CTE Scan on setores_base (cost=0.00..0.02 rows=1 width=16) (never executed)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on setores_base setores_base_1 (cost=0.00..0.02 rows=1 width=16) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Result (cost=7.12..36.75 rows=2 width=32) (never executed)

  • One-Time Filter: (NULLIF(NULL::text, ''::text) IS NOT NULL)
29. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7.12..36.75 rows=2 width=32) (never executed)

  • Hash Cond: (setor_2.idsetorpai = select_setores.idsetor)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using setor_fornec_idx on setor setor_2 (cost=0.29..29.10 rows=28 width=32) (never executed)

  • Index Cond: (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid)
31. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.20..4.20 rows=210 width=16) (never executed)

32. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on select_setores (cost=0.00..4.20 rows=210 width=16) (never executed)

33.          

CTE setores_produto

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.48..10,782.94 rows=19,048 width=32) (never executed)

35. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.92..1.33 rows=41 width=16) (never executed)

  • Group Key: select_setores_1.idsetor
36. 0.000 0.000 ↓ 0.0 0

CTE Scan on select_setores select_setores_1 (cost=0.00..0.82 rows=41 width=16) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using setor_produto_pk on setor_produto (cost=0.56..258.32 rows=465 width=32) (never executed)

  • Index Cond: (idsetor = select_setores_1.idsetor)
  • Heap Fetches: 0
38.          

CTE select_tags_pesquisa

39. 0.007 0.007 ↓ 0.0 0 1

Index Scan using tag_pesquisa_vinculo_produto_idtagpesquisa_idx on tag_pesquisa_vinculo_produto (cost=0.29..2.31 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (idtagpesquisa = ANY (NULL::uuid[]))
40.          

CTE all_products

41. 223.754 6,736.537 ↓ 20.2 7,544 1

Merge Join (cost=4,307.64..43,635.63 rows=374 width=477) (actual time=3,874.174..6,736.537 rows=7,544 loops=1)

  • Merge Cond: (p.idproduto = extra.idproduto)
  • Buffers: shared hit=857,140 read=10,488 dirtied=71 written=4,341
42.          

Initplan (for Merge Join)

43. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: (NULLIF(NULL::text, ''::text) IS NOT NULL)
44. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
45. 2,493.766 2,493.766 ↓ 2.0 831,475 1

Index Scan using produto_pkey on produto p (cost=429.00..38,154.48 rows=425,524 width=62) (actual time=0.017..2,493.766 rows=831,475 loops=1)

  • Filter: (ativo AND ((NULLIF(NULL::text, ''::text) IS NULL) OR (hashed SubPlan 16)))
  • Rows Removed by Filter: 227
  • Buffers: shared hit=834,211 read=2,933 dirtied=71 written=1,038
46.          

SubPlan (for Index Scan)

47. 0.000 0.000 ↓ 0.0 0

CTE Scan on setores_produto (cost=0.00..380.96 rows=19,048 width=16) (never executed)

48. 33.319 3,883.224 ↓ 10.1 7,544 1

Sort (cost=3,684.74..3,686.60 rows=744 width=261) (actual time=3,873.795..3,883.224 rows=7,544 loops=1)

  • Sort Key: extra.idproduto
  • Sort Method: quicksort Memory: 3,847kB
  • Buffers: shared hit=314 read=7,487 written=3,274
49. 3,849.905 3,849.905 ↓ 10.1 7,544 1

Index Scan using cadastro_extra_produto_idfornecedor_disp_idx on cadastro_extra_produto extra (cost=0.57..3,649.25 rows=744 width=261) (actual time=2.529..3,849.905 rows=7,544 loops=1)

  • Index Cond: ((idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid) AND (disponivel = true))
  • Filter: (disponivel AND ((grupos_preco_disponivel = '{}'::character varying[]) OR (grupos_preco_disponivel IS NULL) OR (grupos_preco_disponivel @> '{FNNNSSNNNNN0NNNN|7|206|7}'::character varying[])) AND ((NULLIF(NULL::text, '{}'::text) IS NULL) OR (hashed SubPlan 17)))
  • Buffers: shared hit=314 read=7,487 written=3,274
50.          

SubPlan (for Index Scan)

51. 0.000 0.000 ↓ 0.0 0

CTE Scan on select_tags_pesquisa (cost=0.00..0.02 rows=1 width=16) (never executed)

52.          

SubPlan (for Merge Join)

53. 30.176 135.792 ↑ 1.0 1 7,544

Aggregate (cost=2.44..2.45 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=7,544)

  • Buffers: shared hit=22,615 read=68 written=29
54. 105.616 105.616 ↓ 0.0 0 7,544

Index Only Scan using tag_pesquisa_vinculo_produto_idcadastroextraproduto_idx on tag_pesquisa_vinculo_produto vin (cost=0.41..2.43 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=7,544)

  • Index Cond: (idcadastroextraproduto = extra.idcadastroextraproduto)
  • Heap Fetches: 10
  • Buffers: shared hit=22,615 read=68 written=29
55.          

CTE all_products_text_search

56. 6,783.946 6,783.946 ↓ 40.3 7,544 1

CTE Scan on all_products (cost=0.00..12.16 rows=187 width=1,029) (actual time=3,874.182..6,783.946 rows=7,544 loops=1)

  • Filter: (((NULLIF(NULL::text, ''::text) IS NULL) AND (NULLIF(NULL::text, ''::text) IS NULL)) OR (((codigobarras)::text = NULLIF(NULL::text, ''::text)) AND (NULLIF(NULL::text, ''::text) IS NULL)) OR _has_igualdade_caracteristica)
  • Buffers: shared hit=857,140 read=10,488 dirtied=71 written=4,341
57.          

CTE all_products_search_policy_caracteristicas

58. 6,870.741 36,020.757 ↓ 40.3 7,544 1

CTE Scan on all_products_text_search extra_1 (cost=0.00..34,380.52 rows=187 width=1,093) (actual time=3,880.192..36,020.757 rows=7,544 loops=1)

  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
59.          

SubPlan (for CTE Scan)

60. 15.088 90.528 ↑ 1.0 1 7,544

Aggregate (cost=5.61..5.62 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=7,544)

  • Buffers: shared hit=15,086 read=4 written=1
61. 22.562 75.440 ↓ 0.0 0 7,544

Hash Join (cost=1.53..5.60 rows=3 width=572) (actual time=0.010..0.010 rows=0 loops=7,544)

  • Hash Cond: (vin_1.idcaracteristica = car.idcaracteristica)
  • Buffers: shared hit=15,086 read=4 written=1
62. 52.808 52.808 ↓ 0.0 0 7,544

Index Scan using cuncurrently on vinculo_caracteristica_produto vin_1 (cost=0.29..4.34 rows=3 width=24) (actual time=0.007..0.007 rows=0 loops=7,544)

  • Index Cond: (idcadastroextraproduto = extra_1.idcadastroextraproduto)
  • Buffers: shared hit=15,086 read=3 written=1
63. 0.040 0.070 ↓ 2.1 23 1

Hash (cost=1.11..1.11 rows=11 width=564) (actual time=0.070..0.070 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared read=1
64. 0.030 0.030 ↓ 2.1 23 1

Seq Scan on caracteristica_produto car (cost=0.00..1.11 rows=11 width=564) (actual time=0.026..0.030 rows=23 loops=1)

  • Buffers: shared read=1
65. 37.720 29,059.488 ↑ 1.0 1 7,544

Aggregate (cost=178.20..178.21 rows=1 width=32) (actual time=3.852..3.852 rows=1 loops=7,544)

  • Buffers: shared hit=201,738 read=113,942 dirtied=14,600 written=41,146
66. 9.844 29,021.768 ↓ 0.0 0 7,544

Nested Loop Left Join (cost=1.53..177.77 rows=86 width=576) (actual time=3.752..3.847 rows=0 loops=7,544)

  • Buffers: shared hit=201,738 read=113,942 dirtied=14,600 written=41,146
67. 73.603 28,659.656 ↓ 0.0 0 7,544

Nested Loop (cost=0.97..25.80 rows=1 width=592) (actual time=3.741..3.799 rows=0 loops=7,544)

  • Buffers: shared hit=200,524 read=112,838 dirtied=14,600 written=40,740
68. 28,191.928 28,191.928 ↑ 2.3 3 7,544

Index Scan using politicadesconto_itens_grupo_preco_idx on politicadesconto_itens pdi (cost=0.56..8.67 rows=7 width=16) (actual time=0.729..3.737 rows=3 loops=7,544)

  • Index Cond: (((grupo_preco)::text = 'FNNNSSNNNNN0NNNN|7|206|7'::text) AND ((idprodutoerp)::text = (extra_1.id_produto_erp)::text))
  • Buffers: shared hit=97,121 read=111,127 dirtied=14,600 written=40,097
69. 394.125 394.125 ↓ 0.0 0 26,275

Index Scan using politicadesconto_cabecalho_pk on politicadesconto_cabecalho pdc (cost=0.41..2.44 rows=1 width=576) (actual time=0.015..0.015 rows=0 loops=26,275)

  • Index Cond: (idpoliticacabecalho = pdi.idpoliticacabecalho)
  • Filter: ((NOT usa_cliente) AND (delivery OR (delivery IS NULL)) AND (idfornecedor = extra_1.idfornecedor) AND (now() >= vigencia_inicial) AND (now() <= vigencia_final))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=103,403 read=1,711 written=643
70. 352.268 352.268 ↓ 0.0 0 547

Index Only Scan using politicadesconto_cliente_idpoliticacabecalho_idx on politicadesconto_cliente pcli (cost=0.56..136.46 rows=1,551 width=16) (actual time=0.644..0.644 rows=0 loops=547)

  • Index Cond: (idpoliticacabecalho = pdc.idpoliticacabecalho)
  • Heap Fetches: 0
  • Buffers: shared hit=1,214 read=1,104 written=406
71.          

CTE all_products_with_policy

72. 50.903 36,114.897 ↓ 40.3 7,544 1

Result (cost=0.00..14.96 rows=187 width=1,094) (actual time=3,880.224..36,114.897 rows=7,544 loops=1)

  • One-Time Filter: (NULLIF(NULL::text, '{}'::text) IS NULL)
  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
73. 36,063.994 36,063.994 ↓ 40.3 7,544 1

CTE Scan on all_products_search_policy_caracteristicas (cost=0.00..3.74 rows=187 width=1,093) (actual time=3,880.197..36,063.994 rows=7,544 loops=1)

  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
74.          

CTE correspondence_search_array

75. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

77.          

CTE all_products_with_correspondence_restriction

78. 36,194.535 36,194.543 ↓ 40.3 7,544 1

CTE Scan on all_products_with_policy extra_2 (cost=0.02..1,366.99 rows=187 width=1,107) (actual time=3,880.265..36,194.543 rows=7,544 loops=1)

  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
79.          

SubPlan (for CTE Scan)

80. 0.000 0.000 ↓ 0.0 0 7,544

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=7,544)

  • One-Time Filter: false
81. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=7.28..7.29 rows=1 width=8) (never executed)

82. 0.000 0.000 ↓ 0.0 0

Subquery Scan on a (cost=0.00..7.03 rows=100 width=0) (never executed)

83. 0.000 0.000 ↓ 0.0 0

HashSetOp Intersect (cost=0.00..6.03 rows=100 width=36) (never executed)

84. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..5.53 rows=200 width=36) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=0.00..1.52 rows=100 width=36) (never executed)

86. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.53 rows=100 width=32) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=0.00..3.00 rows=100 width=36) (never executed)

89. 0.000 0.000 ↓ 0.0 0

CTE Scan on correspondence_search_array (cost=0.00..2.00 rows=100 width=32) (never executed)

90. 0.008 0.008 ↓ 0.0 0 1

CTE Scan on select_tags_pesquisa select_tags_pesquisa_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

91.          

CTE all_products_with_match_score

92. 36,249.896 36,249.896 ↓ 40.3 7,544 1

CTE Scan on all_products_with_correspondence_restriction (cost=0.00..10.29 rows=187 width=1,115) (actual time=3,880.273..36,249.896 rows=7,544 loops=1)

  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
93.          

CTE resultado_ordenado

94. 61.025 36,397.067 ↓ 20.0 20 1

Sort (cost=5.71..5.71 rows=1 width=1,093) (actual time=36,397.063..36,397.067 rows=20 loops=1)

  • Sort Key: q.disponivel DESC, ((q._match_score)::double precision) DESC, q.urlimagem DESC, q.prioridade, q.preco
  • Sort Method: quicksort Memory: 4,473kB
  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
95. 25.362 36,336.042 ↓ 7,544.0 7,544 1

Nested Loop Left Join (cost=0.00..5.70 rows=1 width=1,093) (actual time=3,880.285..36,336.042 rows=7,544 loops=1)

  • Join Filter: false
  • Filter: (idcadastroextraproduto IS NULL)
  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
96. 13.384 36,310.680 ↓ 243.4 7,544 1

Subquery Scan on q (cost=0.00..5.39 rows=31 width=1,085) (actual time=3,880.281..36,310.680 rows=7,544 loops=1)

  • Filter: (((NULLIF(NULL::text, ''::text) IS NULL) AND (NULLIF(NULL::text, ''::text) IS NULL)) OR (q._match_score >= 1,000))
  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
97. 36,297.296 36,297.296 ↓ 80.3 7,544 1

CTE Scan on all_products_with_match_score (cost=0.00..3.74 rows=94 width=1,110) (actual time=3,880.279..36,297.296 rows=7,544 loops=1)

  • Filter: (NOT restricao_subitem)
  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
98. 0.000 0.000 ↓ 0.0 0 7,544

Result (cost=0.00..0.00 rows=0 width=16) (actual time=0.000..0.000 rows=0 loops=7,544)

  • One-Time Filter: false
99. 36,397.125 36,397.125 ↓ 20.0 20 1

CTE Scan on resultado_ordenado (cost=0.00..0.02 rows=1 width=1,080) (actual time=36,397.087..36,397.125 rows=20 loops=1)

  • Buffers: shared hit=1,073,964 read=124,434 dirtied=14,671 written=45,488
Planning time : 3.871 ms
Execution time : 36,409.280 ms