explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XjxR

Settings
# exclusive inclusive rows x rows loops node
1. 14.814 7,217.066 ↓ 66.2 16,682 1

HashAggregate (cost=878,636.96..878,639.48 rows=252 width=48) (actual time=7,211.821..7,217.066 rows=16,682 loops=1)

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

CTE classificacaofolha

3. 0.121 0.733 ↑ 11.6 301 1

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

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

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

5.          

CTE cte_classificacao

6. 0.187 0.452 ↑ 20.3 322 1

Recursive Union (cost=0.00..790.74 rows=6,548 width=16) (actual time=0.010..0.452 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.004..0.019 rows=118 loops=1)

8. 0.117 0.246 ↑ 9.5 68 3

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

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

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

10. 0.054 0.078 ↑ 11.0 107 3

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

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

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

12. 0.024 0.050 ↑ 1.0 107 1

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

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

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

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

CTE produto

15. 21.459 40.127 ↑ 1.0 32,651 1

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

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

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

17. 5.569 14.194 ↑ 1.0 28,037 1

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

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

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

19.          

CTE grupocompraitem

20. 5.844 7,172.465 ↑ 1.3 19,189 1

Unique (cost=873,199.53..873,829.14 rows=25,200 width=43) (actual time=7,163.311..7,172.465 rows=19,189 loops=1)

21. 20.026 7,166.621 ↑ 2.0 20,710 1

Sort (cost=873,199.53..873,304.47 rows=41,974 width=43) (actual time=7,163.310..7,166.621 rows=20,710 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, produto_1.produtoid, grupocompra.intervalocompra, grupocompraitem.desconsiderar
  • Sort Method: quicksort Memory: 2386kB
22. 5.433 7,146.595 ↑ 2.0 20,710 1

Hash Join (cost=334.72..869,976.51 rows=41,974 width=43) (actual time=16.407..7,146.595 rows=20,710 loops=1)

  • Hash Cond: (grupocompraitem.grupocompraid = unidadenegocioparticipantegrupocompra.grupocompraid)
23. 6,111.906 7,141.123 ↑ 1.7 19,292 1

Nested Loop (cost=332.25..869,525.10 rows=32,651 width=17) (actual time=16.361..7,141.123 rows=19,292 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: 27342246
24. 49.687 49.687 ↑ 1.0 32,651 1

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

25. 977.901 979.530 ↓ 1.1 838 32,651

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

26. 0.267 1.629 ↓ 1.1 838 1

Merge Left Join (cost=332.25..353.58 rows=760 width=35) (actual time=1.263..1.629 rows=838 loops=1)

  • Merge Cond: (grupocompraitem.classificacaoid = classificacaofolha.classificacaoid)
27. 0.268 0.443 ↓ 1.0 774 1

Sort (cost=55.97..57.87 rows=760 width=35) (actual time=0.343..0.443 rows=774 loops=1)

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

Seq Scan on grupocompraitem (cost=0.00..19.60 rows=760 width=35) (actual time=0.007..0.175 rows=774 loops=1)

29. 0.095 0.919 ↑ 12.8 274 1

Sort (cost=276.29..285.05 rows=3,503 width=16) (actual time=0.896..0.919 rows=274 loops=1)

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

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

31. 0.005 0.039 ↓ 1.4 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.016 0.034 ↓ 1.4 13 1

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

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

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

34. 0.005 0.011 ↓ 1.6 11 1

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

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

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

36.          

CTE grupocompraitemfiltrado

37. 14.615 7,195.987 ↓ 6.9 17,445 1

HashSetOp Except (cost=0.00..1,512.00 rows=2,518 width=46) (actual time=7,190.828..7,195.987 rows=17,445 loops=1)

38. 1.213 7,181.372 ↑ 1.3 19,189 1

Append (cost=0.00..1,260.00 rows=25,200 width=46) (actual time=7,163.318..7,181.372 rows=19,189 loops=1)

39. 1.928 7,178.518 ↓ 1.5 18,317 1

Subquery Scan on *SELECT* 1 (cost=0.00..630.00 rows=12,600 width=46) (actual time=7,163.317..7,178.518 rows=18,317 loops=1)

40. 7,176.590 7,176.590 ↓ 1.5 18,317 1

CTE Scan on grupocompraitem grupocompraitem_1 (cost=0.00..504.00 rows=12,600 width=42) (actual time=7,163.316..7,176.590 rows=18,317 loops=1)

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

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

42. 1.543 1.543 ↑ 14.4 872 1

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

  • Filter: desconsiderar
  • Rows Removed by Filter: 18317
43. 7,202.252 7,202.252 ↓ 6.9 17,445 1

CTE Scan on grupocompraitemfiltrado (cost=0.00..50.36 rows=2,518 width=34) (actual time=7,190.831..7,202.252 rows=17,445 loops=1)