explain.depesz.com

PostgreSQL's explain analyze made readable

Result: v1vy : Optimization for: plan #VzJw

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.068 576.302 ↓ 92.4 15,614 1

HashAggregate (cost=668,783.26..668,784.95 rows=169 width=16) (actual time=575.143..576.302 rows=15,614 loops=1)

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

CTE classificacaofolha

3. 0.146 0.852 ↑ 11.6 301 1

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

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

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

5.          

CTE cte_classificacao

6. 0.213 0.499 ↑ 20.3 322 1

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

7. 0.037 0.037 ↑ 1.0 118 1

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

8. 0.126 0.249 ↑ 9.5 68 3

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

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

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

10. 0.045 0.075 ↑ 11.0 107 3

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

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

12. 0.037 0.089 ↑ 1.0 107 1

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

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

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

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

CTE produto

15. 12.067 37.533 ↑ 1.0 32,651 1

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

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

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

17. 10.025 22.585 ↑ 1.0 28,037 1

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

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

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

19.          

CTE foo

20. 2.153 560.088 ↓ 29.7 16,032 1

Unique (cost=665,514.66..665,521.41 rows=540 width=25) (actual time=556.991..560.088 rows=16,032 loops=1)

21. 8.303 557.935 ↓ 30.0 16,208 1

Sort (cost=665,514.66..665,516.01 rows=540 width=25) (actual time=556.990..557.935 rows=16,208 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, (COALESCE(produto_1.produtoid, grupocompraitem.produtoid)), grupocompraitem.desconsiderar
  • Sort Method: quicksort Memory: 1651kB
22. 374.832 549.632 ↓ 30.0 16,208 1

Nested Loop Left Join (cost=34.57..665,490.15 rows=540 width=25) (actual time=25.120..549.632 rows=16,208 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)) OR (( (...)
  • Rows Removed by Join Filter: 2236713
  • Filter: (COALESCE(produto_1.produtoid, grupocompraitem.produtoid) IS NOT NULL)
  • Rows Removed by Filter: 25
23. 0.095 1.610 ↑ 7.9 69 1

Hash Join (cost=34.57..307.65 rows=543 width=43) (actual time=1.063..1.610 rows=69 loops=1)

  • Hash Cond: (grupocompra.id = unidadenegocioparticipantegrupocompra.grupocompraid)
24. 0.135 1.479 ↑ 6.3 69 1

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

  • Hash Cond: (classificacaofolha.classificacaoid = grupocompraitem.classificacaoid)
25. 0.949 0.949 ↑ 11.6 301 1

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

26. 0.007 0.395 ↑ 86.8 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.210 0.388 ↑ 86.8 5 1

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

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

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

29. 0.003 0.019 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.016 0.016 ↑ 1.0 4 1

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

  • Filter: (id = ANY ('{-1,-2,-3,-4}'::bigint[]))
  • Rows Removed by Filter: 7
31. 0.018 0.036 ↓ 1.4 13 1

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

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

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

33. 173.190 173.190 ↑ 1.0 32,651 69

CTE Scan on produto produto_1 (cost=0.00..653.02 rows=32,651 width=24) (actual time=0.329..2.510 rows=32,651 loops=69)

34.          

CTE bar

35. 4.738 569.766 ↓ 94.9 16,030 1

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

36. 0.747 565.028 ↓ 29.7 16,032 1

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

37. 1.084 563.465 ↓ 59.4 16,031 1

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

38. 562.381 562.381 ↓ 59.4 16,031 1

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

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 1
39. 0.002 0.816 ↑ 270.0 1 1

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

40. 0.814 0.814 ↑ 270.0 1 1

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

  • Filter: desconsiderar
  • Rows Removed by Filter: 16031
41. 572.234 572.234 ↓ 94.9 16,030 1

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

Planning time : 1.976 ms
Execution time : 577.540 ms