explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lp5F

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 72,313.519 ↑ 124.0 5 1

Sort (cost=3,125,760.28..3,125,761.83 rows=620 width=96) (actual time=72,313.518..72,313.519 rows=5 loops=1)

  • Sort Key: (sum(dad.rank))
  • Sort Method: quicksort Memory: 25kB
2. 0.012 72,313.512 ↑ 124.0 5 1

GroupAggregate (cost=3,125,658.62..3,125,731.52 rows=620 width=96) (actual time=72,313.508..72,313.512 rows=5 loops=1)

  • Group Key: (CASE WHEN (dad.rank > 10) THEN 'OUTROS'::text ELSE dad.nome END)
3. 0.007 72,313.500 ↑ 1,241.0 5 1

Sort (cost=3,125,658.62..3,125,674.14 rows=6,205 width=72) (actual time=72,313.499..72,313.500 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 72,313.493 ↑ 1,241.0 5 1

Subquery Scan on dad (cost=3,125,066.07..3,125,267.73 rows=6,205 width=72) (actual time=72,313.486..72,313.493 rows=5 loops=1)

5. 0.022 72,313.490 ↑ 1,241.0 5 1

WindowAgg (cost=3,125,066.07..3,125,190.17 rows=6,205 width=72) (actual time=72,313.484..72,313.490 rows=5 loops=1)

6. 0.008 72,313.468 ↑ 1,241.0 5 1

Sort (cost=3,125,066.07..3,125,081.58 rows=6,205 width=64) (actual time=72,313.467..72,313.468 rows=5 loops=1)

  • Sort Key: (sum(((vei.value ->> 'vei_vl_quantidade'::text))::numeric)) DESC
  • Sort Method: quicksort Memory: 25kB
7. 53.457 72,313.460 ↑ 1,241.0 5 1

HashAggregate (cost=3,124,582.11..3,124,675.18 rows=6,205 width=64) (actual time=72,313.437..72,313.460 rows=5 loops=1)

  • Group Key: (ven.ven_js_dados ->> 'tur_ds_turno'::text)
8. 347.181 72,260.003 ↑ 13.7 45,357 1

Nested Loop (cost=35,129.82..3,116,825.86 rows=620,500 width=64) (actual time=1,153.759..72,260.003 rows=45,357 loops=1)

9. 70,722.117 71,670.636 ↓ 5.6 34,598 1

Bitmap Heap Scan on venda ven (cost=35,129.82..3,102,864.60 rows=6,205 width=1,413) (actual time=1,153.739..71,670.636 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_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. 637.137 637.137 ↑ 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=637.137..637.137 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.558 ms
Execution time : 72,325.017 ms