explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r0G

Settings
# exclusive inclusive rows x rows loops node
1. 0.134 320,837.077 ↓ 43.0 43 1

Sort (cost=60,222.20..60,222.20 rows=1 width=156) (actual time=320,837.073..320,837.077 rows=43 loops=1)

  • Sort Key: (row_number() OVER (?))
  • Sort Method: quicksort Memory: 30kB
2. 0.112 320,836.943 ↓ 43.0 43 1

WindowAgg (cost=60,221.87..60,222.19 rows=1 width=156) (actual time=318,354.046..320,836.943 rows=43 loops=1)

3. 2,339.234 320,836.831 ↓ 43.0 43 1

GroupAggregate (cost=60,221.87..60,222.16 rows=1 width=53) (actual time=318,354.033..320,836.831 rows=43 loops=1)

  • Group Key: patbem.cd_pat_grupo, patgru.no_pat_grupo
  • Filter: ((sum(COALESCE(CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{IA,IC,IM,IE,DP,TE,CM,DE,IO,AO}'::text[])) THEN COALESCE(patbemmov.vl_hist, '0'::numeric) ELSE CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{TS,VE,PE,DM,BO,BA}'::text[])) THEN COALESCE((patbemmov.vl_hist * '-1'::numeric), '0'::numeric) ELSE NULL::numeric END END, '0'::numeric)) <> '0'::numeric) OR (sum(COALESCE(CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{IA,IC,IM,IE,DP,TE,CM,DE,IO,AO}'::text[])) THEN COALESCE(patbemmov.vl_depr_fisc, '0'::numeric) ELSE CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{TS,VE,PE,DM,BO,BA}'::text[])) THEN COALESCE((patbemmov.vl_depr_fisc * '-1'::numeric), '0'::numeric) ELSE NULL::numeric END END, '0'::numeric)) <> '0'::numeric) OR (sum(COALESCE(CASE WHEN (((opepat.id_tipo_operacao)::text = 'DP'::text) AND (patbemmov.dt_movimento >= to_date('2019-01-01'::text, 'yyyy-MM-dd'::text)) AND (patbemmov.dt_movimento <= to_date('2019-01-31'::text, 'yyyy-MM-dd'::text))) THEN COALESCE(patbemmov.vl_depr_fisc, '0'::numeric) ELSE '0'::numeric END, '0'::numeric)) <> '0'::numeric) OR (COALESCE((COALESCE(sum(COALESCE(CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{IA,IC,IM,IE,DP,TE,CM,DE,IO,AO}'::text[])) THEN COALESCE(patbemmov.vl_hist, '0'::numeric) ELSE CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{TS,VE,PE,DM,BO,BA}'::text[])) THEN COALESCE((patbemmov.vl_hist * '-1'::numeric), '0'::numeric) ELSE NULL::numeric END END, '0'::numeric)), '0'::numeric) - COALESCE(sum(COALESCE(CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{IA,IC,IM,IE,DP,TE,CM,DE,IO,AO}'::text[])) THEN COALESCE(patbemmov.vl_depr_fisc, '0'::numeric) ELSE CASE WHEN ((opepat.id_tipo_operacao)::text = ANY ('{TS,VE,PE,DM,BO,BA}'::text[])) THEN COALESCE((patbemmov.vl_depr_fisc * '-1'::numeric), '0'::numeric) ELSE NULL::numeric END END, '0'::numeric)), '0'::numeric)), '0'::numeric) <> '0'::numeric))
  • Rows Removed by Filter: 4
4. 1,716.768 318,497.597 ↓ 1,120,932.0 1,120,932 1

Sort (cost=60,221.87..60,221.88 rows=1 width=53) (actual time=318,353.894..318,497.597 rows=1,120,932 loops=1)

  • Sort Key: patbem.cd_pat_grupo, patgru.no_pat_grupo
  • Sort Method: quicksort Memory: 129527kB
5. 1,393.374 316,780.829 ↓ 1,120,932.0 1,120,932 1

Nested Loop (cost=5,528.07..60,221.86 rows=1 width=53) (actual time=90.596..316,780.829 rows=1,120,932 loops=1)

6. 1,120.020 313,145.591 ↓ 1,120,932.0 1,120,932 1

Nested Loop (cost=5,527.93..60,221.70 rows=1 width=49) (actual time=90.248..313,145.591 rows=1,120,932 loops=1)

7. 1,375.163 306,420.911 ↓ 1,120,932.0 1,120,932 1

Nested Loop (cost=5,527.79..60,221.53 rows=1 width=33) (actual time=89.875..306,420.911 rows=1,120,932 loops=1)

8. 1,640.663 296,078.292 ↓ 1,120,932.0 1,120,932 1

Nested Loop (cost=5,527.50..60,221.20 rows=1 width=45) (actual time=89.203..296,078.292 rows=1,120,932 loops=1)

9. 1,378.934 5,236.915 ↓ 1,120,933.0 1,120,933 1

Hash Anti Join (cost=5,527.08..59,987.45 rows=1 width=37) (actual time=87.243..5,236.915 rows=1,120,933 loops=1)

  • Hash Cond: ((patbemmov.cd_empresa = procan.cd_empresa) AND (patbemmov.cd_base = procan.cd_base) AND (patbemmov.seq_processo = procan.seq_processo))
10. 3,771.420 3,771.420 ↑ 1.0 1,122,195 1

Seq Scan on pat_bem_mov patbemmov (cost=0.00..41,835.62 rows=1,122,200 width=53) (actual time=0.036..3,771.420 rows=1,122,195 loops=1)

  • Filter: ((cd_empresa = 1) AND (dt_movimento <= to_date('2019-01-31'::text, 'yyyy-MM-dd'::text)))
  • Rows Removed by Filter: 12
11. 42.044 86.561 ↑ 1.0 129,268 1

Hash (cost=3,263.70..3,263.70 rows=129,336 width=24) (actual time=86.561..86.561 rows=129,268 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8094kB
12. 44.517 44.517 ↑ 1.0 129,268 1

Seq Scan on processo_canc procan (cost=0.00..3,263.70 rows=129,336 width=24) (actual time=0.041..44.517 rows=129,268 loops=1)

  • Filter: (cd_empresa = 1)
13. 31,853.267 289,200.714 ↑ 1.0 1 1,120,933

Index Only Scan using pat_bem_local_pk on pat_bem_local patbemloc (cost=0.42..233.74 rows=1 width=16) (actual time=0.258..0.258 rows=1 loops=1,120,933)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = patbemmov.cd_bem))
  • Filter: ((to_char((dt_alt_local)::timestamp with time zone, 'YYYYMMDD'::text) || (hr_alt_local)::text) = (SubPlan 1))
  • Rows Removed by Filter: 5
  • Heap Fetches: 1417705
14.          

SubPlan (forIndex Only Scan)

15. 119,258.573 257,347.447 ↑ 1.0 1 6,276,767

Aggregate (cost=46.62..46.63 rows=1 width=11) (actual time=0.041..0.041 rows=1 loops=6,276,767)

16. 32,192.573 138,088.874 ↓ 1.4 7 6,276,767

Nested Loop Anti Join (cost=0.84..46.57 rows=5 width=11) (actual time=0.008..0.022 rows=7 loops=6,276,767)

17. 62,767.670 62,767.670 ↓ 1.4 7 6,276,767

Index Scan using pat_bem_local_pk on pat_bem_local patbemlocaux (cost=0.42..15.54 rows=5 width=35) (actual time=0.005..0.010 rows=7 loops=6,276,767)

  • Index Cond: ((cd_empresa = patbemloc.cd_empresa) AND (cd_bem = patbemloc.cd_bem) AND (dt_alt_local <= to_date('2019-01-31'::text, 'yyyy-MM-dd'::text)))
18. 43,128.631 43,128.631 ↓ 0.0 0 43,128,631

Index Only Scan using processo_canc_pk on processo_canc procan_1 (cost=0.42..5.24 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=43,128,631)

  • Index Cond: ((cd_empresa = patbemlocaux.cd_empresa) AND (cd_empresa = patbemloc.cd_empresa) AND (cd_base = patbemlocaux.cd_base) AND (seq_processo = patbemlocaux.seq_processo))
  • Heap Fetches: 23959
19. 8,967.456 8,967.456 ↑ 1.0 1 1,120,932

Index Scan using pat_bem_pk on pat_bem patbem (cost=0.29..0.32 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=1,120,932)

  • Index Cond: ((cd_empresa = 1) AND (cd_bem = patbemmov.cd_bem))
20. 5,604.660 5,604.660 ↑ 1.0 1 1,120,932

Index Scan using pat_grupo_pk on pat_grupo patgru (cost=0.14..0.16 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=1,120,932)

  • Index Cond: ((cd_empresa = 1) AND ((cd_pat_grupo)::text = (patbem.cd_pat_grupo)::text))
21. 2,241.864 2,241.864 ↑ 1.0 1 1,120,932

Index Scan using operacao_patrimonio_pk on operacao_patrimonio opepat (cost=0.14..0.16 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=1,120,932)

  • Index Cond: (cd_operacao = patbemmov.cd_operacao)