explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Aj1

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 47,674.557 ↑ 202.0 5 1

Sort (cost=1,277,630.45..1,277,632.97 rows=1,010 width=96) (actual time=47,674.557..47,674.557 rows=5 loops=1)

  • Sort Key: (sum(dad.rank))
  • Sort Method: quicksort Memory: 25kB
2. 0.010 47,674.551 ↑ 202.0 5 1

GroupAggregate (cost=1,277,461.37..1,277,580.05 rows=1,010 width=96) (actual time=47,674.546..47,674.551 rows=5 loops=1)

  • Group Key: (CASE WHEN (dad.rank > 10) THEN 'OUTROS'::text ELSE dad.nome END)
3. 0.007 47,674.541 ↑ 2,020.0 5 1

Sort (cost=1,277,461.37..1,277,486.62 rows=10,100 width=72) (actual time=47,674.541..47,674.541 rows=5 loops=1)

  • Sort Key: (CASE WHEN (dad.rank > 10) THEN 'OUTROS'::text ELSE dad.nome END)
  • Sort Method: quicksort Memory: 25kB
4. 0.003 47,674.534 ↑ 2,020.0 5 1

Subquery Scan on dad (cost=1,276,461.37..1,276,789.62 rows=10,100 width=72) (actual time=47,674.527..47,674.534 rows=5 loops=1)

5. 0.014 47,674.531 ↑ 2,020.0 5 1

WindowAgg (cost=1,276,461.37..1,276,663.37 rows=10,100 width=72) (actual time=47,674.526..47,674.531 rows=5 loops=1)

6. 0.011 47,674.517 ↑ 2,020.0 5 1

Sort (cost=1,276,461.37..1,276,486.62 rows=10,100 width=64) (actual time=47,674.517..47,674.517 rows=5 loops=1)

  • Sort Key: (sum(((vei.value ->> 'vei_vl_quantidade'::text))::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
7. 30.533 47,674.506 ↑ 2,020.0 5 1

GroupAggregate (cost=1,275,486.61..1,275,789.61 rows=10,100 width=64) (actual time=47,655.937..47,674.506 rows=5 loops=1)

  • Group Key: ((ven.ven_js_dados ->> 'tur_ds_turno'::text))
8. 30.338 47,643.973 ↓ 4.5 45,357 1

Sort (cost=1,275,486.61..1,275,511.86 rows=10,100 width=64) (actual time=47,641.870..47,643.973 rows=45,357 loops=1)

  • Sort Key: ((ven.ven_js_dados ->> 'tur_ds_turno'::text))
  • Sort Method: quicksort Memory: 92,894kB
9. 336.852 47,613.635 ↓ 4.5 45,357 1

Nested Loop (cost=323.36..1,274,814.86 rows=10,100 width=64) (actual time=90.716..47,613.635 rows=45,357 loops=1)

10. 19.961 47,034.597 ↓ 342.6 34,598 1

Nested Loop (cost=323.36..1,274,587.60 rows=101 width=1,413) (actual time=90.693..47,034.597 rows=34,598 loops=1)

11. 52.998 55.776 ↓ 1.2 20,180 1

Bitmap Heap Scan on caixa cai (cost=251.86..11,311.20 rows=17,339 width=8) (actual time=3.261..55.776 rows=20,180 loops=1)

  • Recheck Cond: ((cai_dt_movimento >= (CURRENT_DATE - '30 days'::interval)) AND (cai_dt_movimento <= CURRENT_DATE))
  • Heap Blocks: exact=3,495
12. 2.778 2.778 ↓ 1.2 20,183 1

Bitmap Index Scan on caixa_idx_data_pdv_turno (cost=0.00..247.52 rows=17,339 width=0) (actual time=2.778..2.778 rows=20,183 loops=1)

  • Index Cond: ((cai_dt_movimento >= (CURRENT_DATE - '30 days'::interval)) AND (cai_dt_movimento <= CURRENT_DATE))
13. 426.596 46,958.860 ↓ 2.0 2 20,180

Bitmap Heap Scan on venda ven (cost=71.50..72.85 rows=1 width=1,421) (actual time=2.278..2.327 rows=2 loops=20,180)

  • Recheck Cond: ((ven_cd_caixa = cai.cai_cd_caixa) AND (ven_cd_unidade_negocio = cai.cai_cd_unidade_negocio) AND (ven_cd_unidade_negocio = ANY ('{183,185,180}'::integer[])))
  • Filter: ((ven_fl_cancelada = 'N'::bpchar) AND (SubPlan 1))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=31,833
14. 905.468 45,909.500 ↓ 0.0 0 20,180

BitmapAnd (cost=71.50..71.50 rows=1 width=0) (actual time=2.275..2.275 rows=0 loops=20,180)

15. 322.880 322.880 ↑ 3.3 98 20,180

Bitmap Index Scan on venda_idx_caixa (cost=0.00..4.08 rows=328 width=0) (actual time=0.016..0.016 rows=98 loops=20,180)

  • Index Cond: (ven_cd_caixa = cai.cai_cd_caixa)
16. 44,681.152 44,681.152 ↓ 2.4 11,853 19,666

Bitmap Index Scan on venda_ven_cd_unidade_negocio_idx (cost=0.00..67.17 rows=4,994 width=0) (actual time=2.272..2.272 rows=11,853 loops=19,666)

  • Index Cond: ((ven_cd_unidade_negocio = cai.cai_cd_unidade_negocio) AND (ven_cd_unidade_negocio = ANY ('{183,185,180}'::integer[])))
17.          

SubPlan (for Bitmap Heap Scan)

18. 622.764 622.764 ↑ 18.0 1 34,598

Function Scan on jsonb_array_elements vei_1 (cost=0.01..4.00 rows=18 width=0) (actual time=0.018..0.018 rows=1 loops=34,598)

  • Filter: (((value ->> 'pro_cd_produto_grupo'::text))::integer = ANY ('{1653,1624,1657,1655,1630,1633,1634,1637,1619,1638,1641,1621,1642,1643,1620,1646,1651,1658}'::integer[]))
19. 242.186 242.186 ↑ 100.0 1 34,598

Function Scan on jsonb_array_elements vei (cost=0.01..1.00 rows=100 width=32) (actual time=0.007..0.007 rows=1 loops=34,598)

Planning time : 5.066 ms
Execution time : 47,681.713 ms