explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4fa1

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.615 3,068.239 ↓ 9.6 1,859 1

HashAggregate (cost=480,675.44..480,677.37 rows=193 width=48) (actual time=3,068.056..3,068.239 rows=1,859 loops=1)

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

CTE grupocompraitem

3. 0.433 3,066.143 ↓ 2.3 2,204 1

Unique (cost=480,596.88..480,611.54 rows=977 width=43) (actual time=3,065.617..3,066.143 rows=2,204 loops=1)

4. 1.054 3,065.710 ↓ 2.3 2,212 1

Sort (cost=480,596.88..480,599.32 rows=977 width=43) (actual time=3,065.617..3,065.710 rows=2,212 loops=1)

  • Sort Key: grupocompra.id, unidadenegocioparticipantegrupocompra.unidadenegocioid, produto.id, grupocompra.intervalocompra, grupocompraitem.desconsiderar
  • Sort Method: quicksort Memory: 269kB
5. 1.249 3,064.656 ↓ 2.3 2,212 1

Nested Loop (cost=0.00..480,548.36 rows=977 width=43) (actual time=0.165..3,064.656 rows=2,212 loops=1)

  • Join Filter: (grupocompraitem.grupocompraid = unidadenegocioparticipantegrupocompra.grupocompraid)
  • Rows Removed by Join Filter: 17888
6. 0.768 3,062.067 ↓ 1.8 1,340 1

Nested Loop (cost=0.00..480,444.65 rows=760 width=43) (actual time=0.156..3,062.067 rows=1,340 loops=1)

  • Join Filter: (grupocompraitem.grupocompraid = grupocompra.id)
  • Rows Removed by Join Filter: 13400
7. 2,391.580 3,059.959 ↓ 1.8 1,340 1

Nested Loop Left Join (cost=0.00..480,363.76 rows=760 width=17) (actual time=0.144..3,059.959 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
8. 0.159 0.159 ↓ 1.0 777 1

Seq Scan on grupocompraitem (cost=0.00..19.60 rows=760 width=27) (actual time=0.020..0.159 rows=777 loops=1)

9. 661.834 668.220 ↑ 1.0 28,037 777

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

10. 6.386 6.386 ↑ 1.0 28,037 1

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

11. 1.331 1.340 ↓ 1.6 11 1,340

Materialize (cost=0.00..1.10 rows=7 width=26) (actual time=0.000..0.001 rows=11 loops=1,340)

12. 0.009 0.009 ↓ 1.6 11 1

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

13. 1.328 1.340 ↓ 1.7 15 1,340

Materialize (cost=0.00..1.14 rows=9 width=16) (actual time=0.000..0.001 rows=15 loops=1,340)

14. 0.012 0.012 ↓ 1.7 15 1

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

15.          

CTE grupocompraitemfiltrado

16. 0.562 3,067.356 ↓ 11.4 2,196 1

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

17. 0.091 3,066.794 ↓ 2.3 2,204 1

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

18. 0.166 3,066.595 ↓ 4.5 2,200 1

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

19. 3,066.429 3,066.429 ↓ 4.5 2,200 1

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

  • Filter: (NOT desconsiderar)
  • Rows Removed by Filter: 4
20. 0.002 0.108 ↑ 122.0 4 1

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

21. 0.106 0.106 ↑ 122.0 4 1

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

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

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

Planning time : 1.274 ms
Execution time : 3,068.634 ms