explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XTXn

Settings
# exclusive inclusive rows x rows loops node
1. 21.257 318.575 ↓ 240.0 46,317 1

HashAggregate (cost=15,689.85..15,691.78 rows=193 width=48) (actual time=310.081..318.575 rows=46,317 loops=1)

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

CTE classificacaofolha

3. 0.267 1.417 ↑ 11.6 301 1

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

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

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

5.          

CTE cte_classificacao

6. 0.326 0.866 ↑ 20.3 322 1

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

7. 0.045 0.045 ↑ 1.0 118 1

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

8. 0.252 0.495 ↑ 9.5 68 3

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

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

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

10. 0.093 0.150 ↑ 11.0 107 3

Hash (cost=23.60..23.60 rows=1,180 width=16) (actual time=0.050..0.050 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.036 0.102 ↑ 1.0 107 1

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

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

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

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

CTE produto

15. 11.110 37.249 ↑ 1.0 32,651 1

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

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

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

17. 10.310 23.596 ↑ 1.0 28,037 1

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

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

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

19.          

CTE grupocompraitem

20. 10.101 247.568 ↓ 53.5 52,309 1

Unique (cost=12,384.71..12,399.36 rows=977 width=43) (actual time=231.839..247.568 rows=52,309 loops=1)

21. 44.755 237.467 ↓ 54.5 53,223 1

Sort (cost=12,384.71..12,387.15 rows=977 width=43) (actual time=231.838..237.467 rows=53,223 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, (COALESCE(produtoa.produtoid, produtob.produtoid, produtoc.produtoid, grupocompraitem.produtoid)), grupocompra.intervalocompra, grupocompraitem.desconsiderar
  • Sort Method: external merge Disk: 2080kB
22. 7.687 192.712 ↓ 54.5 53,223 1

Hash Join (cost=6,936.15..12,336.19 rows=977 width=43) (actual time=139.426..192.712 rows=53,223 loops=1)

  • Hash Cond: (grupocompraitem.grupocompraid = grupocompra.id)
23. 43.303 184.944 ↓ 24.9 18,887 1

Hash Right Join (cost=6,933.69..12,321.10 rows=760 width=41) (actual time=139.335..184.944 rows=18,887 loops=1)

  • Hash Cond: (produtob.classificacaoid = classificacaofolha.classificacaofolhaid)
  • Join Filter: (grupocompraitem.tipo = 'B'::bpchar)
  • Rows Removed by Join Filter: 453575
24. 2.322 2.322 ↑ 1.0 32,651 1

CTE Scan on produto produtob (cost=0.00..653.02 rows=32,651 width=16) (actual time=0.002..2.322 rows=32,651 loops=1)

25. 0.316 139.319 ↓ 3.1 2,363 1

Hash (cost=6,924.19..6,924.19 rows=760 width=43) (actual time=139.319..139.319 rows=2,363 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 179kB
26. 41.609 139.003 ↓ 3.1 2,363 1

Merge Left Join (cost=6,591.95..6,924.19 rows=760 width=43) (actual time=63.459..139.003 rows=2,363 loops=1)

  • Merge Cond: (grupocompraitem.fabricanteid = produtoc.fabricanteid)
  • Join Filter: ((grupocompraitem.tipo = 'C'::bpchar) AND (produtoc.classificacaoid = classificacaofolha.classificacaofolhaid))
  • Rows Removed by Join Filter: 425020
27. 3.817 62.596 ↓ 2.6 1,939 1

Merge Left Join (cost=3,490.95..3,658.01 rows=760 width=43) (actual time=54.807..62.596 rows=1,939 loops=1)

  • Merge Cond: (grupocompraitem.fabricanteid = produtoa.fabricanteid)
  • Join Filter: (grupocompraitem.tipo = 'A'::bpchar)
  • Rows Removed by Join Filter: 20040
28. 0.428 3.550 ↓ 1.1 841 1

Sort (cost=389.94..391.84 rows=760 width=35) (actual time=3.487..3.550 rows=841 loops=1)

  • Sort Key: grupocompraitem.fabricanteid
  • Sort Method: quicksort Memory: 90kB
29. 0.478 3.122 ↓ 1.1 841 1

Merge Left Join (cost=332.25..353.58 rows=760 width=35) (actual time=2.455..3.122 rows=841 loops=1)

  • Merge Cond: (grupocompraitem.classificacaoid = classificacaofolha.classificacaoid)
30. 0.483 0.835 ↓ 1.0 777 1

Sort (cost=55.97..57.87 rows=760 width=35) (actual time=0.670..0.835 rows=777 loops=1)

  • Sort Key: grupocompraitem.classificacaoid
  • Sort Method: quicksort Memory: 85kB
31. 0.352 0.352 ↓ 1.0 777 1

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

32. 0.201 1.809 ↑ 10.5 335 1

Sort (cost=276.29..285.05 rows=3,503 width=16) (actual time=1.736..1.809 rows=335 loops=1)

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

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

34. 9.521 55.229 ↓ 1.6 52,199 1

Sort (cost=3,101.00..3,182.63 rows=32,651 width=16) (actual time=51.124..55.229 rows=52,199 loops=1)

  • Sort Key: produtoa.fabricanteid
  • Sort Method: quicksort Memory: 2299kB
35. 45.708 45.708 ↑ 1.0 32,651 1

CTE Scan on produto produtoa (cost=0.00..653.02 rows=32,651 width=16) (actual time=23.684..45.708 rows=32,651 loops=1)

36. 31.432 34.798 ↓ 14.0 456,515 1

Sort (cost=3,101.00..3,182.63 rows=32,651 width=24) (actual time=8.353..34.798 rows=456,515 loops=1)

  • Sort Key: produtoc.fabricanteid
  • Sort Method: quicksort Memory: 3319kB
37. 3.366 3.366 ↑ 1.0 32,651 1

CTE Scan on produto produtoc (cost=0.00..653.02 rows=32,651 width=24) (actual time=0.000..3.366 rows=32,651 loops=1)

38. 0.009 0.081 ↓ 1.7 15 1

Hash (cost=2.35..2.35 rows=9 width=42) (actual time=0.081..0.081 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.027 0.072 ↓ 1.7 15 1

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

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

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

41. 0.014 0.026 ↓ 1.6 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.012 0.012 ↓ 1.6 11 1

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

43.          

CTE grupocompraitemfiltrado

44. 21.583 288.553 ↓ 243.9 47,081 1

HashSetOp Except (cost=0.00..58.60 rows=193 width=46) (actual time=281.083..288.553 rows=47,081 loops=1)

45. 2.401 266.970 ↓ 53.6 52,309 1

Append (cost=0.00..48.84 rows=976 width=46) (actual time=231.844..266.970 rows=52,309 loops=1)

46. 3.742 260.368 ↓ 101.8 49,695 1

Subquery Scan on *SELECT* 1 (cost=0.00..24.42 rows=488 width=46) (actual time=231.843..260.368 rows=49,695 loops=1)

47. 256.626 256.626 ↓ 101.8 49,695 1

CTE Scan on grupocompraitem grupocompraitem_1 (cost=0.00..19.54 rows=488 width=42) (actual time=231.843..256.626 rows=49,695 loops=1)

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 2614
48. 0.215 4.201 ↓ 5.4 2,614 1

Subquery Scan on *SELECT* 2 (cost=0.00..24.42 rows=488 width=46) (actual time=0.049..4.201 rows=2,614 loops=1)

49. 3.986 3.986 ↓ 5.4 2,614 1

CTE Scan on grupocompraitem grupocompraitem_2 (cost=0.00..19.54 rows=488 width=42) (actual time=0.049..3.986 rows=2,614 loops=1)

  • Filter: desconsiderar
  • Rows Removed by Filter: 49695
50. 297.318 297.318 ↓ 243.9 47,081 1

CTE Scan on grupocompraitemfiltrado (cost=0.00..3.86 rows=193 width=34) (actual time=281.086..297.318 rows=47,081 loops=1)