explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QtB : Optimization for: Optimization for: plan #VzJw; plan #v1vy

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4.024 673.924 ↓ 78.1 15,614 1

HashAggregate (cost=505,516.04..505,518.04 rows=200 width=16) (actual time=672.784..673.924 rows=15,614 loops=1)

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

CTE classificacaofolha

3. 0.163 0.861 ↑ 11.6 301 1

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

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

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

5.          

CTE cte_classificacao

6. 0.193 0.515 ↑ 20.3 322 1

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

7. 0.028 0.028 ↑ 1.0 118 1

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

8. 0.156 0.294 ↑ 9.5 68 3

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

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

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

10. 0.054 0.081 ↑ 11.0 107 3

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

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

12. 0.036 0.076 ↑ 1.0 107 1

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

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

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

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

CTE produto

15. 13.973 43.866 ↑ 1.0 32,651 1

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

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

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

17. 13.071 27.577 ↑ 1.0 28,037 1

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

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

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

19.          

CTE foo

20. 2.080 657.846 ↓ 1.1 16,032 1

Unique (cost=501,133.89..501,425.49 rows=14,400 width=25) (actual time=654.509..657.846 rows=16,032 loops=1)

21. 9.375 655.766 ↑ 1.4 16,209 1

Sort (cost=501,133.89..501,192.21 rows=23,328 width=25) (actual time=654.508..655.766 rows=16,209 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, produto_1.produtoid, grupocompraitem.desconsiderar
  • Sort Method: quicksort Memory: 1651kB
22. 23.077 646.391 ↑ 1.4 16,209 1

Nested Loop (cost=33.37..499,441.47 rows=23,328 width=25) (actual time=29.417..646.391 rows=16,209 loops=1)

  • Join Filter: (grupocompra.id = unidadenegocioparticipantegrupocompra.grupocompraid)
  • Rows Removed by Join Filter: 194508
23. 507.378 623.314 ↑ 1.2 16,209 1

Nested Loop (cost=33.37..496,923.28 rows=18,645 width=25) (actual time=29.397..623.314 rows=16,209 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: 2236710
24. 50.634 50.634 ↑ 1.0 32,651 1

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

25. 63.894 65.302 ↑ 6.3 69 32,651

Materialize (cost=33.37..302.65 rows=434 width=43) (actual time=0.000..0.002 rows=69 loops=32,651)

26. 0.121 1.408 ↑ 6.3 69 1

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

  • Hash Cond: (classificacaofolha.classificacaoid = grupocompraitem.classificacaoid)
27. 0.961 0.961 ↑ 11.6 301 1

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

28. 0.006 0.326 ↑ 86.8 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.175 0.320 ↑ 86.8 5 1

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

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

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

31. 0.006 0.023 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.017 0.017 ↑ 1.0 4 1

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

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

Materialize (cost=0.00..1.14 rows=9 width=16) (actual time=0.000..0.000 rows=13 loops=16,209)

34. 0.007 0.007 ↓ 1.4 13 1

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

35.          

CTE bar

36. 4.688 667.402 ↓ 11.1 16,030 1

HashSetOp Except (cost=0.00..828.00 rows=1,439 width=28) (actual time=666.222..667.402 rows=16,030 loops=1)

37. 0.745 662.714 ↓ 1.1 16,032 1

Append (cost=0.00..720.00 rows=14,400 width=28) (actual time=654.512..662.714 rows=16,032 loops=1)

38. 1.127 661.170 ↓ 2.2 16,031 1

Subquery Scan on *SELECT* 1 (cost=0.00..360.00 rows=7,200 width=28) (actual time=654.512..661.170 rows=16,031 loops=1)

39. 660.043 660.043 ↓ 2.2 16,031 1

CTE Scan on foo (cost=0.00..288.00 rows=7,200 width=24) (actual time=654.510..660.043 rows=16,031 loops=1)

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 1
40. 0.001 0.799 ↑ 7,200.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..360.00 rows=7,200 width=28) (actual time=0.794..0.799 rows=1 loops=1)

41. 0.798 0.798 ↑ 7,200.0 1 1

CTE Scan on foo foo_1 (cost=0.00..288.00 rows=7,200 width=24) (actual time=0.793..0.798 rows=1 loops=1)

  • Filter: desconsiderar
  • Rows Removed by Filter: 16031
42. 669.900 669.900 ↓ 11.1 16,030 1

CTE Scan on bar (cost=0.00..28.78 rows=1,439 width=16) (actual time=666.224..669.900 rows=16,030 loops=1)

Planning time : 2.497 ms
Execution time : 675.222 ms