explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VzJw

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.414 174.125 ↓ 92.4 15,614 1

HashAggregate (cost=12,844.70..12,846.39 rows=169 width=16) (actual time=172.788..174.125 rows=15,614 loops=1)

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

CTE classificacaofolha

3. 0.266 1.543 ↑ 11.6 301 1

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

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

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

5.          

CTE cte_classificacao

6. 0.347 0.924 ↑ 20.3 322 1

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

7. 0.043 0.043 ↑ 1.0 118 1

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

8. 0.267 0.534 ↑ 9.5 68 3

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

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

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

10. 0.108 0.165 ↑ 11.0 107 3

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

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

12. 0.053 0.114 ↑ 1.0 107 1

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

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

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

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

CTE produto

15. 12.679 40.201 ↑ 1.0 32,651 1

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

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

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

17. 11.612 24.686 ↑ 1.0 28,037 1

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

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

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

19.          

CTE foo

20. 2.023 156.700 ↓ 29.7 16,032 1

Unique (cost=9,576.10..9,582.85 rows=540 width=25) (actual time=153.374..156.700 rows=16,032 loops=1)

21. 8.142 154.677 ↓ 30.0 16,208 1

Sort (cost=9,576.10..9,577.45 rows=540 width=25) (actual time=153.372..154.677 rows=16,208 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, (COALESCE(produtoclassificacaofabricante.produtoid, produtoclassificacao.produtoid, produtofabricante.produtoid, grupocompraitem.produtoid)), grupocompraitem. (...)
  • Sort Method: quicksort Memory: 1651kB
22. 2.855 146.535 ↓ 30.0 16,208 1

Hash Join (cost=6,170.35..9,551.59 rows=540 width=25) (actual time=96.933..146.535 rows=16,208 loops=1)

  • Hash Cond: (grupocompra.id = unidadenegocioparticipantegrupocompra.grupocompraid)
23. 44.787 143.551 ↓ 37.5 16,208 1

Hash Right Join (cost=6,169.15..9,544.45 rows=432 width=49) (actual time=96.794..143.551 rows=16,208 loops=1)

  • Hash Cond: (produtoclassificacao.classificacaoid = classificacaofolha.classificacaofolhaid)
  • Join Filter: (grupocompraitem.tipo = 'B'::bpchar)
  • Rows Removed by Join Filter: 447493
  • Filter: (COALESCE(produtoclassificacaofabricante.produtoid, produtoclassificacao.produtoid, produtofabricante.produtoid, grupocompraitem.produtoid) IS NOT NULL)
  • Rows Removed by Filter: 25
24. 2.008 2.008 ↑ 1.0 32,651 1

CTE Scan on produto produtoclassificacao (cost=0.00..653.02 rows=32,651 width=16) (actual time=0.000..2.008 rows=32,651 loops=1)

25. 0.132 96.756 ↓ 1.9 824 1

Hash (cost=6,163.73..6,163.73 rows=434 width=51) (actual time=96.756..96.756 rows=824 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 70kB
26. 29.301 96.624 ↓ 1.9 824 1

Hash Right Join (cost=3,674.07..6,163.73 rows=434 width=51) (actual time=65.166..96.624 rows=824 loops=1)

  • Hash Cond: (produtofabricante.fabricanteid = grupocompraitem.fabricanteid)
  • Join Filter: (grupocompraitem.tipo = 'A'::bpchar)
  • Rows Removed by Join Filter: 264385
27. 2.250 2.250 ↑ 1.0 32,651 1

CTE Scan on produto produtofabricante (cost=0.00..653.02 rows=32,651 width=16) (actual time=0.001..2.250 rows=32,651 loops=1)

28. 0.073 65.073 ↓ 1.1 473 1

Hash (cost=3,668.65..3,668.65 rows=434 width=51) (actual time=65.073..65.073 rows=473 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
29. 1.110 65.000 ↓ 1.1 473 1

Merge Left Join (cost=3,420.50..3,668.65 rows=434 width=51) (actual time=64.834..65.000 rows=473 loops=1)

  • Merge Cond: ((grupocompraitem.fabricanteid = produtoclassificacaofabricante.fabricanteid) AND (classificacaofolha.classificacaofolhaid = produtoclassificacaofabricante.classificacaoid))
  • Join Filter: (grupocompraitem.tipo = 'C'::bpchar)
30. 0.166 2.580 ↑ 6.3 69 1

Sort (cost=319.50..320.58 rows=434 width=43) (actual time=2.576..2.580 rows=69 loops=1)

  • Sort Key: grupocompraitem.fabricanteid, classificacaofolha.classificacaofolhaid
  • Sort Method: quicksort Memory: 30kB
31. 0.175 2.414 ↑ 6.3 69 1

Hash Right Join (cost=33.37..300.48 rows=434 width=43) (actual time=1.221..2.414 rows=69 loops=1)

  • Hash Cond: (classificacaofolha.classificacaoid = grupocompraitem.classificacaoid)
32. 1.751 1.751 ↑ 11.6 301 1

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

33. 0.011 0.488 ↑ 86.8 5 1

Hash (cost=27.95..27.95 rows=434 width=43) (actual time=0.488..0.488 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.255 0.477 ↑ 86.8 5 1

Hash Join (cost=1.16..27.95 rows=434 width=43) (actual time=0.085..0.477 rows=5 loops=1)

  • Hash Cond: (grupocompraitem.grupocompraid = grupocompra.id)
35. 0.183 0.183 ↓ 1.0 766 1

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

36. 0.010 0.039 ↑ 1.0 4 1

Hash (cost=1.10..1.10 rows=4 width=8) (actual time=0.039..0.039 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.029 0.029 ↑ 1.0 4 1

Seq Scan on grupocompra (cost=0.00..1.10 rows=4 width=8) (actual time=0.027..0.029 rows=4 loops=1)

  • Filter: (id = ANY ('{-1,-2,-3,-4}'::bigint[]))
  • Rows Removed by Filter: 7
38. 11.548 61.310 ↑ 2.3 13,918 1

Sort (cost=3,101.00..3,182.63 rows=32,651 width=24) (actual time=60.258..61.310 rows=13,918 loops=1)

  • Sort Key: produtoclassificacaofabricante.fabricanteid, produtoclassificacaofabricante.classificacaoid
  • Sort Method: quicksort Memory: 3319kB
39. 49.762 49.762 ↑ 1.0 32,651 1

CTE Scan on produto produtoclassificacaofabricante (cost=0.00..653.02 rows=32,651 width=24) (actual time=25.376..49.762 rows=32,651 loops=1)

40. 0.103 0.129 ↓ 1.4 13 1

Hash (cost=1.09..1.09 rows=9 width=16) (actual time=0.129..0.129 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.026 0.026 ↓ 1.4 13 1

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

42.          

CTE bar

43. 5.442 167.001 ↓ 94.9 16,030 1

HashSetOp Except (cost=0.00..31.05 rows=169 width=28) (actual time=165.197..167.001 rows=16,030 loops=1)

44. 0.791 161.559 ↓ 29.7 16,032 1

Append (cost=0.00..27.00 rows=540 width=28) (actual time=153.377..161.559 rows=16,032 loops=1)

45. 1.162 160.009 ↓ 59.4 16,031 1

Subquery Scan on *SELECT* 1 (cost=0.00..13.50 rows=270 width=28) (actual time=153.376..160.009 rows=16,031 loops=1)

46. 158.847 158.847 ↓ 59.4 16,031 1

CTE Scan on foo (cost=0.00..10.80 rows=270 width=24) (actual time=153.375..158.847 rows=16,031 loops=1)

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 1
47. 0.001 0.759 ↑ 270.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..13.50 rows=270 width=28) (actual time=0.754..0.759 rows=1 loops=1)

48. 0.758 0.758 ↑ 270.0 1 1

CTE Scan on foo foo_1 (cost=0.00..10.80 rows=270 width=24) (actual time=0.753..0.758 rows=1 loops=1)

  • Filter: desconsiderar
  • Rows Removed by Filter: 16031
49. 169.711 169.711 ↓ 94.9 16,030 1

CTE Scan on bar (cost=0.00..3.38 rows=169 width=16) (actual time=165.199..169.711 rows=16,030 loops=1)