explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cToq

Settings
# exclusive inclusive rows x rows loops node
1. 0.606 3,174.793 ↓ 9.6 1,859 1

HashAggregate (cost=482,095.36..482,097.29 rows=193 width=48) (actual time=3,174.604..3,174.793 rows=1,859 loops=1)

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

CTE classificacaofolha

3. 0.154 1.015 ↑ 11.6 301 1

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

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

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

5.          

CTE cte_classificacao

6. 0.224 0.670 ↑ 20.3 322 1

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

7. 0.032 0.032 ↑ 1.0 118 1

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

8. 0.243 0.414 ↑ 9.5 68 3

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

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

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

10. 0.063 0.099 ↑ 11.0 107 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 21kB
11. 0.036 0.036 ↑ 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.012 rows=107 loops=3)

12. 0.019 0.045 ↑ 1.0 107 1

Hash (cost=3.18..3.18 rows=107 width=8) (actual time=0.045..0.045 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.006..0.026 rows=107 loops=1)

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

CTE grupocompraitem

15. 0.446 3,172.640 ↓ 2.3 2,204 1

Unique (cost=480,965.51..480,980.17 rows=977 width=43) (actual time=3,172.087..3,172.640 rows=2,204 loops=1)

16. 1.220 3,172.194 ↓ 2.4 2,340 1

Sort (cost=480,965.51..480,967.96 rows=977 width=43) (actual time=3,172.086..3,172.194 rows=2,340 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, produto.id, grupocompra.intervalocompra, grupocompraitem.desconsiderar
  • Sort Method: quicksort Memory: 279kB
17. 1.696 3,170.974 ↓ 2.4 2,340 1

Nested Loop (cost=115.00..480,916.99 rows=977 width=43) (actual time=1.379..3,170.974 rows=2,340 loops=1)

  • Join Filter: (grupocompra.id = grupocompraitem.grupocompraid)
  • Rows Removed by Join Filter: 18720
18. 0.319 3,167.874 ↓ 1.8 1,404 1

Hash Left Join (cost=113.85..480,812.02 rows=760 width=17) (actual time=1.351..3,167.874 rows=1,404 loops=1)

  • Hash Cond: (grupocompraitem.classificacaoid = classificacaofolha.classificacaoid)
19. 2,487.001 3,166.290 ↓ 1.8 1,340 1

Nested Loop Left Join (cost=0.00..480,363.76 rows=760 width=25) (actual time=0.073..3,166.290 rows=1,340 loops=1)

  • Join Filter: (((grupocompraitem.tipo = 'D'::bpchar) AND (produto.id = grupocompraitem.produtoid)) OR ((grupocompraitem.tipo = 'A'::bpchar) AND (produto.fabricanteid = grupocompraitem.fabricanteid)))
  • Rows Removed by Join Filter: 21783418
20. 0.191 0.191 ↓ 1.0 777 1

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

21. 672.021 679.098 ↑ 1.0 28,037 777

Materialize (cost=0.00..981.56 rows=28,037 width=16) (actual time=0.000..0.874 rows=28,037 loops=777)

22. 7.077 7.077 ↑ 1.0 28,037 1

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

23. 0.083 1.265 ↑ 11.6 301 1

Hash (cost=70.06..70.06 rows=3,503 width=8) (actual time=1.265..1.265 rows=301 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 44kB
24. 1.182 1.182 ↑ 11.6 301 1

CTE Scan on classificacaofolha (cost=0.00..70.06 rows=3,503 width=8) (actual time=0.063..1.182 rows=301 loops=1)

25. 1.369 1.404 ↓ 1.7 15 1,404

Materialize (cost=1.16..2.40 rows=9 width=42) (actual time=0.000..0.001 rows=15 loops=1,404)

26. 0.015 0.035 ↓ 1.7 15 1

Hash Join (cost=1.16..2.35 rows=9 width=42) (actual time=0.024..0.035 rows=15 loops=1)

  • Hash Cond: (unidadenegocioparticipantegrupocompra.grupocompraid = grupocompra.id)
27. 0.010 0.010 ↓ 1.7 15 1

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

28. 0.004 0.010 ↓ 1.6 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 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)

30.          

CTE grupocompraitemfiltrado

31. 0.641 3,173.902 ↓ 11.4 2,196 1

HashSetOp Except (cost=0.00..58.60 rows=193 width=46) (actual time=3,173.783..3,173.902 rows=2,196 loops=1)

32. 0.088 3,173.261 ↓ 2.3 2,204 1

Append (cost=0.00..48.84 rows=976 width=46) (actual time=3,172.090..3,173.261 rows=2,204 loops=1)

33. 0.146 3,173.059 ↓ 4.5 2,200 1

Subquery Scan on *SELECT* 1 (cost=0.00..24.42 rows=488 width=46) (actual time=3,172.090..3,173.059 rows=2,200 loops=1)

34. 3,172.913 3,172.913 ↓ 4.5 2,200 1

CTE Scan on grupocompraitem grupocompraitem_1 (cost=0.00..19.54 rows=488 width=42) (actual time=3,172.089..3,172.913 rows=2,200 loops=1)

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 4
35. 0.001 0.114 ↑ 122.0 4 1

Subquery Scan on *SELECT* 2 (cost=0.00..24.42 rows=488 width=46) (actual time=0.002..0.114 rows=4 loops=1)

36. 0.113 0.113 ↑ 122.0 4 1

CTE Scan on grupocompraitem grupocompraitem_2 (cost=0.00..19.54 rows=488 width=42) (actual time=0.001..0.113 rows=4 loops=1)

  • Filter: desconsiderar
  • Rows Removed by Filter: 2200
37. 3,174.187 3,174.187 ↓ 11.4 2,196 1

CTE Scan on grupocompraitemfiltrado (cost=0.00..3.86 rows=193 width=34) (actual time=3,173.784..3,174.187 rows=2,196 loops=1)