explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hfAN

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

Nested Loop Left Join (cost=896,856.91..896,967.81 rows=8 width=603) (actual time=36,525.380..36,525.380 rows=0 loops=1)

  • Join Filter: pdc.usa_cliente
  • Buffers: shared hit=24,382 read=687,711 dirtied=9,422 written=9,387, temp written=27,578
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.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,307.64..43,635.63 rows=374 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=429.00..38,154.48 rows=425,524 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..380.96 rows=19,048 width=16) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,684.74..3,686.60 rows=744 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,649.25 rows=744 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.44..2.45 rows=1 width=32) (never executed)

54. 0.000 0.000 ↓ 0.0 0

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) (never executed)

  • Index Cond: (idcadastroextraproduto = extra.idcadastroextraproduto)
  • Heap Fetches: 0
55.          

CTE politicas_fornecedor

56. 1,544.787 34,282.412 ↓ 14.1 3,670,239 1

Hash Join (cost=926.49..835,531.59 rows=259,802 width=52) (actual time=1,730.375..34,282.412 rows=3,670,239 loops=1)

  • Hash Cond: (pdi.idpoliticacabecalho = pdc_1.idpoliticacabecalho)
  • Buffers: shared hit=24,382 read=687,711 dirtied=9,422 written=9,387
57. 32,541.583 32,541.583 ↑ 1.9 5,093,902 1

Seq Scan on politicadesconto_itens pdi (cost=0.00..808,940.44 rows=9,775,644 width=52) (actual time=946.029..32,541.583 rows=5,093,902 loops=1)

  • Buffers: shared hit=24,025 read=687,159 dirtied=9,422 written=9,387
58. 0.746 196.042 ↑ 1.3 624 1

Hash (cost=916.42..916.42 rows=805 width=16) (actual time=196.042..196.042 rows=624 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 38kB
  • Buffers: shared hit=357 read=552
59. 195.296 195.296 ↑ 1.3 624 1

Index Scan using politicadesconto_cabecalho_idfornecedor_idx on politicadesconto_cabecalho pdc_1 (cost=0.42..916.42 rows=805 width=16) (actual time=1.310..195.296 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=357 read=552
60. 0.001 36,525.379 ↓ 0.0 0 1

Nested Loop (cost=6,495.48..6,509.74 rows=1 width=577) (actual time=36,525.379..36,525.379 rows=0 loops=1)

  • Buffers: shared hit=24,382 read=687,711 dirtied=9,422 written=9,387, temp written=27,578
61. 0.003 36,525.378 ↓ 0.0 0 1

HashAggregate (cost=6,495.07..6,495.13 rows=6 width=16) (actual time=36,525.378..36,525.378 rows=0 loops=1)

  • Group Key: pf.idpolitica
  • Buffers: shared hit=24,382 read=687,711 dirtied=9,422 written=9,387, temp written=27,578
62. 36,525.375 36,525.375 ↓ 0.0 0 1

CTE Scan on politicas_fornecedor pf (cost=0.00..6,495.05 rows=6 width=16) (actual time=36,525.374..36,525.375 rows=0 loops=1)

  • Filter: (((grupo_preco)::text = 'FNNNSSNNNNN0NNNN|7|206|7'::text) AND ((idprodutoerp)::text = ''::text))
  • Rows Removed by Filter: 3,670,239
  • Buffers: shared hit=24,382 read=687,711 dirtied=9,422 written=9,387, temp written=27,578
63. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (idpoliticacabecalho = pf.idpolitica)
  • Filter: ((NOT usa_cliente) AND (idfornecedor = '11fa19b2-fc88-49c6-ad0c-8a764042f2cf'::uuid))
64. 0.000 0.000 ↓ 0.0 0

Index Only Scan using politicadesconto_cliente_idpoliticacabecalho_idx on politicadesconto_cliente pcli (cost=0.56..81.68 rows=1,551 width=16) (never executed)

  • Index Cond: (idpoliticacabecalho = pdc.idpoliticacabecalho)
  • Heap Fetches: 0
Planning time : 2.461 ms
Execution time : 36,564.681 ms