explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Liia

Settings
# exclusive inclusive rows x rows loops node
1. 5.646 5,995.677 ↓ 66.2 16,684 1

HashAggregate (cost=878,730.85..878,733.37 rows=252 width=48) (actual time=5,993.894..5,995.677 rows=16,684 loops=1)

  • Group Key: grupocompraitemfiltrado.unidadenegocioid, grupocompraitemfiltrado.produtoid
2.          

CTE classificacaofolha

3. 0.104 0.641 ↑ 11.6 301 1

Hash Join (cost=795.26..1,051.28 rows=3,503 width=16) (actual time=0.073..0.641 rows=301 loops=1)

  • Hash Cond: (cte_classificacao.classificacaodescendenteid = classificacao_1.id)
4. 0.490 0.490 ↑ 20.3 322 1

CTE Scan on cte_classificacao (cost=790.74..921.70 rows=6,548 width=16) (actual time=0.007..0.490 rows=322 loops=1)

5.          

CTE cte_classificacao

6. 0.158 0.396 ↑ 20.3 322 1

Recursive Union (cost=0.00..790.74 rows=6,548 width=16) (actual time=0.006..0.396 rows=322 loops=1)

7. 0.019 0.019 ↑ 1.0 118 1

Seq Scan on classificacao (cost=0.00..3.18 rows=118 width=16) (actual time=0.003..0.019 rows=118 loops=1)

8. 0.117 0.219 ↑ 9.5 68 3

Hash Join (cost=38.35..65.66 rows=643 width=16) (actual time=0.042..0.073 rows=68 loops=3)

  • Hash Cond: (c.classificacaopaiid = cf.classificacaodescendenteid)
9. 0.036 0.036 ↑ 1.0 118 3

Seq Scan on classificacao c (cost=0.00..3.18 rows=118 width=16) (actual time=0.003..0.012 rows=118 loops=3)

10. 0.045 0.066 ↑ 11.0 107 3

Hash (cost=23.60..23.60 rows=1,180 width=16) (actual time=0.022..0.022 rows=107 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 21kB
11. 0.021 0.021 ↑ 11.0 107 3

WorkTable Scan on cte_classificacao cf (cost=0.00..23.60 rows=1,180 width=16) (actual time=0.000..0.007 rows=107 loops=3)

12. 0.020 0.047 ↑ 1.0 107 1

Hash (cost=3.18..3.18 rows=107 width=8) (actual time=0.047..0.047 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
13. 0.027 0.027 ↑ 1.0 107 1

Seq Scan on classificacao classificacao_1 (cost=0.00..3.18 rows=107 width=8) (actual time=0.007..0.027 rows=107 loops=1)

  • Filter: folha
  • Rows Removed by Filter: 11
14.          

CTE produto

15. 15.982 30.829 ↑ 1.0 32,651 1

Hash Join (cost=1,191.83..2,175.29 rows=32,651 width=24) (actual time=12.059..30.829 rows=32,651 loops=1)

  • Hash Cond: (classificacaoproduto.produtoid = produto.id)
16. 2.821 2.821 ↑ 1.0 32,651 1

Seq Scan on classificacaoproduto (cost=0.00..534.51 rows=32,651 width=16) (actual time=0.006..2.821 rows=32,651 loops=1)

17. 5.374 12.026 ↑ 1.0 28,037 1

Hash (cost=841.37..841.37 rows=28,037 width=16) (actual time=12.026..12.026 rows=28,037 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1571kB
18. 6.652 6.652 ↑ 1.0 28,037 1

Seq Scan on produto (cost=0.00..841.37 rows=28,037 width=16) (actual time=0.004..6.652 rows=28,037 loops=1)

19.          

CTE grupocompraitem

20. 3.694 5,975.294 ↑ 1.3 19,191 1

Unique (cost=873,293.32..873,923.02 rows=25,200 width=43) (actual time=5,969.808..5,975.294 rows=19,191 loops=1)

21. 12.287 5,971.600 ↑ 2.0 20,712 1

Sort (cost=873,293.32..873,398.27 rows=41,980 width=43) (actual time=5,969.808..5,971.600 rows=20,712 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, produto_1.produtoid, grupocompra.intervalocompra, grupocompraitem.desconsiderar
  • Sort Method: quicksort Memory: 2387kB
22. 3.467 5,959.313 ↑ 2.0 20,712 1

Hash Join (cost=334.72..870,069.80 rows=41,980 width=43) (actual time=13.976..5,959.313 rows=20,712 loops=1)

  • Hash Cond: (grupocompraitem.grupocompraid = grupocompra.id)
23. 5,069.202 5,955.816 ↑ 1.7 19,294 1

Nested Loop (cost=332.25..869,525.10 rows=32,651 width=17) (actual time=13.938..5,955.816 rows=19,294 loops=1)

  • Join Filter: (((grupocompraitem.tipo = 'A'::bpchar) AND (produto_1.fabricanteid = grupocompraitem.fabricanteid)) OR ((grupocompraitem.tipo = 'B'::bpchar) AND (produto_1.classificacaoid = classificacaofolha.classificacaofolhaid)) (...)
  • Rows Removed by Join Filter: 27440197
24. 37.688 37.688 ↑ 1.0 32,651 1

CTE Scan on produto produto_1 (cost=0.00..653.02 rows=32,651 width=24) (actual time=12.060..37.688 rows=32,651 loops=1)

25. 847.488 848.926 ↓ 1.1 841 32,651

Materialize (cost=332.25..357.38 rows=760 width=35) (actual time=0.000..0.026 rows=841 loops=32,651)

26. 0.204 1.438 ↓ 1.1 841 1

Merge Left Join (cost=332.25..353.58 rows=760 width=35) (actual time=1.149..1.438 rows=841 loops=1)

  • Merge Cond: (grupocompraitem.classificacaoid = classificacaofolha.classificacaoid)
27. 0.261 0.407 ↓ 1.0 777 1

Sort (cost=55.97..57.87 rows=760 width=35) (actual time=0.327..0.407 rows=777 loops=1)

  • Sort Key: grupocompraitem.classificacaoid
  • Sort Method: quicksort Memory: 85kB
28. 0.146 0.146 ↓ 1.0 777 1

Seq Scan on grupocompraitem (cost=0.00..19.60 rows=760 width=35) (actual time=0.006..0.146 rows=777 loops=1)

29. 0.106 0.827 ↑ 10.5 335 1

Sort (cost=276.29..285.05 rows=3,503 width=16) (actual time=0.801..0.827 rows=335 loops=1)

  • Sort Key: classificacaofolha.classificacaoid
  • Sort Method: quicksort Memory: 39kB
30. 0.721 0.721 ↑ 11.6 301 1

CTE Scan on classificacaofolha (cost=0.00..70.06 rows=3,503 width=16) (actual time=0.073..0.721 rows=301 loops=1)

31. 0.003 0.030 ↓ 1.4 13 1

Hash (cost=2.35..2.35 rows=9 width=42) (actual time=0.030..0.030 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.013 0.027 ↓ 1.4 13 1

Hash Join (cost=1.16..2.35 rows=9 width=42) (actual time=0.021..0.027 rows=13 loops=1)

  • Hash Cond: (unidadenegocioparticipantegrupocompra.grupocompraid = grupocompra.id)
33. 0.006 0.006 ↓ 1.4 13 1

Seq Scan on unidadenegocioparticipantegrupocompra (cost=0.00..1.09 rows=9 width=16) (actual time=0.006..0.006 rows=13 loops=1)

34. 0.003 0.008 ↓ 1.6 11 1

Hash (cost=1.07..1.07 rows=7 width=26) (actual time=0.008..0.008 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.005 0.005 ↓ 1.6 11 1

Seq Scan on grupocompra (cost=0.00..1.07 rows=7 width=26) (actual time=0.003..0.005 rows=11 loops=1)

36.          

CTE grupocompraitemfiltrado

37. 6.276 5,987.331 ↓ 6.9 17,447 1

HashSetOp Except (cost=0.00..1,512.00 rows=2,518 width=46) (actual time=5,985.878..5,987.331 rows=17,447 loops=1)

38. 0.878 5,981.055 ↑ 1.3 19,191 1

Append (cost=0.00..1,260.00 rows=25,200 width=46) (actual time=5,969.811..5,981.055 rows=19,191 loops=1)

39. 1.249 5,979.082 ↓ 1.5 18,319 1

Subquery Scan on *SELECT* 1 (cost=0.00..630.00 rows=12,600 width=46) (actual time=5,969.810..5,979.082 rows=18,319 loops=1)

40. 5,977.833 5,977.833 ↓ 1.5 18,319 1

CTE Scan on grupocompraitem grupocompraitem_1 (cost=0.00..504.00 rows=12,600 width=42) (actual time=5,969.809..5,977.833 rows=18,319 loops=1)

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 872
41. 0.072 1.095 ↑ 14.4 872 1

Subquery Scan on *SELECT* 2 (cost=0.00..630.00 rows=12,600 width=46) (actual time=0.050..1.095 rows=872 loops=1)

42. 1.023 1.023 ↑ 14.4 872 1

CTE Scan on grupocompraitem grupocompraitem_2 (cost=0.00..504.00 rows=12,600 width=42) (actual time=0.049..1.023 rows=872 loops=1)

  • Filter: desconsiderar
  • Rows Removed by Filter: 18319
43. 5,990.031 5,990.031 ↓ 6.9 17,447 1

CTE Scan on grupocompraitemfiltrado (cost=0.00..50.36 rows=2,518 width=34) (actual time=5,985.880..5,990.031 rows=17,447 loops=1)

Planning time : 0.972 ms
Execution time : 5,996.778 ms