explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NKQI

Settings
# exclusive inclusive rows x rows loops node
1. 1.508 16.550 ↓ 55.7 15,441 1

Unique (cost=1,995.79..1,997.87 rows=277 width=16) (actual time=14.326..16.550 rows=15,441 loops=1)

2. 5.825 15.042 ↓ 55.8 15,443 1

Sort (cost=1,995.79..1,996.49 rows=277 width=16) (actual time=14.325..15.042 rows=15,443 loops=1)

  • Sort Key: unidadenegocioparticipantegrupocompra.unidadenegocioid, (COALESCE(produtofabricante.id, classificacaoproduto.produtoid, grupocompraitem.produtoid))
  • Sort Method: quicksort Memory: 1108kB
3. 1.401 9.217 ↓ 55.8 15,443 1

Nested Loop Left Join (cost=1,016.50..1,984.56 rows=277 width=16) (actual time=4.748..9.217 rows=15,443 loops=1)

4. 0.011 4.892 ↓ 34.0 34 1

Nested Loop Left Join (cost=1,016.21..1,964.98 rows=1 width=32) (actual time=4.742..4.892 rows=34 loops=1)

5. 0.021 4.847 ↓ 34.0 34 1

Nested Loop (cost=1,016.07..1,964.81 rows=1 width=32) (actual time=4.738..4.847 rows=34 loops=1)

6. 0.024 4.792 ↓ 34.0 34 1

Nested Loop (cost=1,016.07..1,963.68 rows=1 width=32) (actual time=4.735..4.792 rows=34 loops=1)

7. 2.492 4.734 ↓ 34.0 34 1

Hash Right Join (cost=1,016.07..1,962.59 rows=1 width=32) (actual time=4.727..4.734 rows=34 loops=1)

  • Hash Cond: (produtofabricante.fabricanteid = grupocompraitem.fabricanteid)
  • Join Filter: (grupocompraitem.tipo = 'A'::bpchar)
8. 1.942 1.942 ↑ 1.0 28,037 1

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

9. 0.008 0.300 ↓ 34.0 34 1

Hash (cost=1,016.05..1,016.05 rows=1 width=34) (actual time=0.300..0.300 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.032 0.292 ↓ 34.0 34 1

Hash Right Join (cost=795.05..1,016.05 rows=1 width=34) (actual time=0.029..0.292 rows=34 loops=1)

  • Hash Cond: (cte_classificacao.classificacaoancestralid = grupocompraitem.classificacaoid)
  • Join Filter: (grupocompraitem.tipo = 'B'::bpchar)
11. 0.251 0.251 ↑ 20.3 322 1

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

12.          

CTE cte_classificacao

13. 0.079 0.203 ↑ 20.3 322 1

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

14. 0.013 0.013 ↑ 1.0 118 1

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

15. 0.057 0.111 ↑ 9.5 68 3

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

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

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

17. 0.021 0.036 ↑ 11.0 107 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 21kB
18. 0.015 0.015 ↑ 11.0 107 3

WorkTable Scan on cte_classificacao cf (cost=0.00..23.60 rows=1,180 width=16) (actual time=0.000..0.005 rows=107 loops=3)

19. 0.003 0.009 ↑ 1.0 1 1

Hash (cost=4.29..4.29 rows=1 width=34) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.006 0.006 ↑ 1.0 1 1

Index Scan using idx_grupocompraitem_grupocompraid on grupocompraitem (cost=0.28..4.29 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (grupocompraid = '-2'::integer)
21. 0.034 0.034 ↑ 1.0 1 34

Seq Scan on grupocompra (cost=0.00..1.09 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=34)

  • Filter: (id = '-2'::integer)
  • Rows Removed by Filter: 8
22. 0.034 0.034 ↑ 1.0 1 34

Seq Scan on unidadenegocioparticipantegrupocompra (cost=0.00..1.11 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=34)

  • Filter: (grupocompraid = '-2'::integer)
  • Rows Removed by Filter: 10
23. 0.034 0.034 ↑ 1.0 1 34

Index Scan using pk_classificacao on classificacao (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=34)

  • Index Cond: (id = cte_classificacao.classificacaodescendenteid)
  • Filter: folha
  • Rows Removed by Filter: 0
24. 2.924 2.924 ↓ 1.4 454 34

Index Scan using idx_classificacaoproduto_classificacaoid on classificacaoproduto (cost=0.29..16.31 rows=327 width=16) (actual time=0.003..0.086 rows=454 loops=34)

  • Index Cond: (classificacaoid = classificacao.id)
Planning time : 0.536 ms
Execution time : 17.112 ms