explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uvMc

Settings
# exclusive inclusive rows x rows loops node
1. 27,831.956 27,831.956 ↓ 438.3 105,638 1

CTE Scan on cabecalho (cost=977,712.53..977,717.35 rows=241 width=254) (actual time=27,245.545..27,831.956 rows=105,638 loops=1)

2.          

CTE empresa

3. 0.100 0.100 ↑ 4.0 1 1

Index Only Scan using spsemp00_pkey on spsemp00 (cost=0.14..12.47 rows=4 width=12) (actual time=0.052..0.100 rows=1 loops=1)

  • Filter: CASE WHEN ("position"(',1,'::text, lower((((','::text || textin(varcharout((textin(numeric_out(cod)))::character varying))) || ','::text))::text)) > 0) THEN true ELSE false END
  • Rows Removed by Filter: 8
  • Heap Fetches: 9
4.          

CTE cabecalho

5. 146.320 27,715.489 ↓ 438.3 105,638 1

GroupAggregate (cost=977,680.17..977,700.05 rows=241 width=265) (actual time=27,245.539..27,715.489 rows=105,638 loops=1)

  • Group Key: 0, (('now'::cstring)::date), 0, 0, 0, p0.trs, p0.ope, p0.ven, p0.cmv, p0.cli, p0.mtr, p0.nr_nfcenumero, p1.pro, p1.flg, p1.icm, p1.tbp
6. 2,577.201 27,569.169 ↓ 444.0 107,005 1

Sort (cost=977,680.17..977,680.77 rows=241 width=87) (actual time=27,245.511..27,569.169 rows=107,005 loops=1)

  • Sort Key: p0.trs, p0.ope, p0.ven, p0.cmv, p0.cli, p0.mtr, p0.nr_nfcenumero, p1.pro, p1.flg, p1.icm, p1.tbp
  • Sort Method: external merge Disk: 10112kB
7. 431.282 24,991.968 ↓ 444.0 107,005 1

Nested Loop (cost=1,265.77..977,670.64 rows=241 width=87) (actual time=50.065..24,991.968 rows=107,005 loops=1)

8. 12.365 760.160 ↑ 3.4 16,369 1

Nested Loop (cost=1,265.20..510,459.92 rows=55,364 width=63) (actual time=34.514..760.160 rows=16,369 loops=1)

9. 0.108 0.108 ↑ 4.0 1 1

CTE Scan on empresa e0 (cost=0.00..0.08 rows=4 width=12) (actual time=0.057..0.108 rows=1 loops=1)

10. 720.220 747.687 ↓ 1.2 16,369 1

Bitmap Heap Scan on pdvcup00 p0 (cost=1,265.20..127,476.55 rows=13,841 width=51) (actual time=34.451..747.687 rows=16,369 loops=1)

  • Recheck Cond: ((emp = e0.cod) AND (dta >= '2017-02-01'::date) AND (dta <= '2017-02-28'::date))
  • Filter: ((pdv > '0'::numeric) AND (num > '0'::numeric) AND (hrm >= '0'::numeric) AND (hrm <= '24'::numeric) AND (ope > '0'::numeric))
  • Heap Blocks: exact=5469
11. 27.467 27.467 ↑ 2.5 16,369 1

Bitmap Index Scan on pdvcup00_001 (cost=0.00..1,261.74 rows=41,534 width=0) (actual time=27.467..27.467 rows=16,369 loops=1)

  • Index Cond: ((emp = e0.cod) AND (dta >= '2017-02-01'::date) AND (dta <= '2017-02-28'::date))
12. 23,800.526 23,800.526 ↓ 7.0 7 16,369

Index Scan using pdvcup01_pkey on pdvcup01 p1 (cost=0.57..8.43 rows=1 width=56) (actual time=1.078..1.454 rows=7 loops=16,369)

  • Index Cond: ((emp = p0.emp) AND (dta = p0.dta) AND (pdv = p0.pdv) AND (num = p0.num))