explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4kR9

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 36,059.642 ↓ 0.0 0 1

Nested Loop (cost=1,043,610.37..1,043,631.97 rows=1 width=602) (actual time=36,059.642..36,059.642 rows=0 loops=1)

  • Buffers: shared hit=36,713 read=762,449 dirtied=1,428 written=1,737, temp written=33,339
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..2,069.55 rows=18,836 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..45.85 rows=459 width=32) (never executed)

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

CTE select_tags_pesquisa

39. 0.000 0.000 ↓ 0.0 0

Index Scan using tag_pesquisa_vinculo_produto_idtagpesquisa_idx on tag_pesquisa_vinculo_produto (cost=0.29..2.31 rows=1 width=16) (never executed)

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

CTE all_products

41. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=4,458.19..43,811.59 rows=392 width=477) (never executed)

  • Merge Cond: (p.idproduto = extra.idproduto)
42.          

Initplan (for Merge Join)

43. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Index Scan using produto_pkey on produto p (cost=424.24..38,178.98 rows=425,857 width=62) (never executed)

  • Filter: (ativo AND ((NULLIF(NULL::text, ''::text) IS NULL) OR (hashed SubPlan 16)))
46.          

SubPlan (for Index Scan)

47. 0.000 0.000 ↓ 0.0 0

CTE Scan on setores_produto (cost=0.00..376.72 rows=18,836 width=16) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,839.87..3,841.82 rows=780 width=261) (never executed)

  • Sort Key: extra.idproduto
49. 0.000 0.000 ↓ 0.0 0

Index Scan using cadastro_extra_produto_idfornecedor_disp_idx on cadastro_extra_produto extra (cost=0.57..3,802.40 rows=780 width=261) (never executed)

  • 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)))
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. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=32) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Index Scan using tag_pesquisa_vinculo_produto_idcadastroextraproduto_idx_2 on tag_pesquisa_vinculo_produto vin (cost=0.29..2.31 rows=1 width=16) (never executed)

  • Index Cond: (idcadastroextraproduto = extra.idcadastroextraproduto)
55.          

CTE politicas_fornecedor

56. 1,570.649 33,582.233 ↓ 11.9 4,436,954 1

Hash Join (cost=1,123.84..988,025.89 rows=371,687 width=52) (actual time=2,583.247..33,582.233 rows=4,436,954 loops=1)

  • Hash Cond: (pdi.idpoliticacabecalho = pdc_1.idpoliticacabecalho)
  • Buffers: shared hit=36,713 read=762,449 dirtied=1,428 written=1,737
57. 31,724.444 31,724.444 ↑ 2.5 5,863,224 1

Seq Scan on politicadesconto_itens pdi (cost=0.00..947,726.04 rows=14,922,604 width=52) (actual time=502.475..31,724.444 rows=5,863,224 loops=1)

  • Buffers: shared hit=36,618 read=761,882 dirtied=1,428 written=1,536
58. 1.829 287.140 ↑ 1.6 624 1

Hash (cost=1,111.45..1,111.45 rows=991 width=16) (actual time=287.140..287.140 rows=624 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
  • Buffers: shared hit=95 read=567 written=201
59. 285.311 285.311 ↑ 1.6 624 1

Index Scan using politicadesconto_cabecalho_idfornecedor_idx on politicadesconto_cabecalho pdc_1 (cost=0.42..1,111.45 rows=991 width=16) (actual time=3.027..285.311 rows=624 loops=1)

  • Index Cond: ((idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid) AND (now() >= vigencia_inicial) AND (now() >= vigencia_inicial) AND (now() <= vigencia_final) AND (now() <= vigencia_final))
  • Filter: (delivery OR (delivery IS NULL))
  • Buffers: shared hit=95 read=567 written=201
60. 0.003 36,059.641 ↓ 0.0 0 1

HashAggregate (cost=9,292.20..9,292.29 rows=9 width=16) (actual time=36,059.641..36,059.641 rows=0 loops=1)

  • Group Key: pf.idpolitica
  • Buffers: shared hit=36,713 read=762,449 dirtied=1,428 written=1,737, temp written=33,339
61. 36,059.638 36,059.638 ↓ 0.0 0 1

CTE Scan on politicas_fornecedor pf (cost=0.00..9,292.18 rows=9 width=16) (actual time=36,059.638..36,059.638 rows=0 loops=1)

  • Filter: (((grupo_preco)::text = 'FNNNSSNNNNN0NNNN|7|206|7'::text) AND ((idprodutoerp)::text = ''::text))
  • Rows Removed by Filter: 4,436,954
  • Buffers: shared hit=36,713 read=762,449 dirtied=1,428 written=1,737, temp written=33,339
62. 0.000 0.000 ↓ 0.0 0

Index Scan using politicadesconto_cabecalho_pk on politicadesconto_cabecalho pdc (cost=0.41..2.44 rows=1 width=575) (never executed)

  • Index Cond: (idpoliticacabecalho = pf.idpolitica)
  • Filter: (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid)
Planning time : 22.364 ms
Execution time : 36,076.660 ms