explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l5vH

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 84,870.292 ↑ 123.4 5 1

Sort (cost=3,131,850.32..3,131,851.86 rows=617 width=96) (actual time=84,870.292..84,870.292 rows=5 loops=1)

  • Sort Key: (sum(dad.rank))
  • Sort Method: quicksort Memory: 25kB
2. 0.012 84,870.285 ↑ 123.4 5 1

GroupAggregate (cost=3,131,749.18..3,131,821.72 rows=617 width=96) (actual time=84,870.281..84,870.285 rows=5 loops=1)

  • Group Key: (CASE WHEN (dad.rank > 10) THEN 'OUTROS'::text ELSE dad.nome END)
3. 0.008 84,870.273 ↑ 1,234.8 5 1

Sort (cost=3,131,749.18..3,131,764.62 rows=6,174 width=72) (actual time=84,870.272..84,870.273 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 84,870.265 ↑ 1,234.8 5 1

Subquery Scan on dad (cost=3,131,159.81..3,131,360.47 rows=6,174 width=72) (actual time=84,870.259..84,870.265 rows=5 loops=1)

5. 0.021 84,870.262 ↑ 1,234.8 5 1

WindowAgg (cost=3,131,159.81..3,131,283.29 rows=6,174 width=72) (actual time=84,870.257..84,870.262 rows=5 loops=1)

6. 0.008 84,870.241 ↑ 1,234.8 5 1

Sort (cost=3,131,159.81..3,131,175.25 rows=6,174 width=64) (actual time=84,870.241..84,870.241 rows=5 loops=1)

  • Sort Key: (sum(((vei.value ->> 'vei_vl_quantidade'::text))::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
7. 52.754 84,870.233 ↑ 1,234.8 5 1

HashAggregate (cost=3,130,678.49..3,130,771.10 rows=6,174 width=64) (actual time=84,870.211..84,870.233 rows=5 loops=1)

  • Group Key: (ven.ven_js_dados ->> 'tur_ds_turno'::text)
8. 321.996 84,817.479 ↑ 13.6 45,357 1

Nested Loop (cost=35,129.81..3,122,960.99 rows=617,400 width=64) (actual time=1,159.647..84,817.479 rows=45,357 loops=1)

9. 83,301.411 84,253.297 ↓ 5.6 34,598 1

Bitmap Heap Scan on venda ven (cost=35,129.81..3,109,069.48 rows=6,174 width=1,413) (actual time=1,159.627..84,253.297 rows=34,598 loops=1)

  • Recheck Cond: ((ven_cd_unidade_negocio = ANY ('{183,185,180}'::integer[])) AND (ven_fl_cancelada = 'N'::bpchar))
  • Filter: (((ven_js_dados ->> 'cai_cd_turno'::text) IS NOT NULL) AND (((ven_js_dados ->> 'cai_dt_movimento'::text))::date <= CURRENT_DATE) AND (((ven_js_dados ->> 'cai_dt_movimento'::text))::date >= (CURRENT_DATE - '30 days'::interval)) AND (SubPlan 1))
  • Rows Removed by Filter: 2295909
  • Heap Blocks: exact=1722726
10. 640.504 640.504 ↑ 1.1 2,330,507 1

Bitmap Index Scan on venda_ven_cd_unidade_negocio_ven_fl_cancelada_ven_dt_fiscal_ven (cost=0.00..35,128.26 rows=2,481,956 width=0) (actual time=640.504..640.504 rows=2,330,507 loops=1)

  • Index Cond: ((ven_cd_unidade_negocio = ANY ('{183,185,180}'::integer[])) AND (ven_fl_cancelada = 'N'::bpchar))
11.          

SubPlan (for Bitmap Heap Scan)

12. 311.382 311.382 ↑ 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.009..0.009 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[]))
13. 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 : 0.629 ms
Execution time : 84,881.997 ms