explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ob8j

Settings
# exclusive inclusive rows x rows loops node
1. 400,154.618 400,154.618 ↓ 31.2 15,007 1

CTE Scan on dadospro (cost=2,243,521.63..2,243,531.25 rows=481 width=368) (actual time=379,045.769..400,154.618 rows=15,007 loops=1)

2.          

CTE empresa

3. 0.067 0.067 ↑ 1.0 9 1

Index Only Scan using spsemp00_pkey on spsemp00 (cost=0.14..12.27 rows=9 width=12) (actual time=0.019..0.067 rows=9 loops=1)

  • Heap Fetches: 9
4.          

CTE cabecalho

5. 3,675.463 327,967.949 ↓ 4,523.9 2,176,015 1

GroupAggregate (cost=2,239,624.48..2,239,664.17 rows=481 width=265) (actual time=313,672.855..327,967.949 rows=2,176,015 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. 50,331.721 324,292.486 ↓ 4,611.9 2,218,301 1

Sort (cost=2,239,624.48..2,239,625.69 rows=481 width=87) (actual time=313,672.836..324,292.486 rows=2,218,301 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: 205592kB
7. 5,773.488 273,960.765 ↓ 4,611.9 2,218,301 1

Nested Loop (cost=1,444.25..2,239,603.06 rows=481 width=87) (actual time=5.887..273,960.765 rows=2,218,301 loops=1)

8. 164.842 7,929.629 ↓ 2.7 299,836 1

Nested Loop (cost=1,443.68..1,305,841.37 rows=110,736 width=63) (actual time=5.778..7,929.629 rows=299,836 loops=1)

9. 0.109 0.109 ↑ 1.0 9 1

CTE Scan on empresa e0 (cost=0.00..0.18 rows=9 width=12) (actual time=0.025..0.109 rows=9 loops=1)

10. 7,542.657 7,764.678 ↓ 2.1 33,315 9

Bitmap Heap Scan on pdvcup00 p0 (cost=1,443.68..144,935.28 rows=15,819 width=51) (actual time=30.712..862.742 rows=33,315 loops=9)

  • Recheck Cond: ((emp = e0.cod) AND (dta >= '2017-01-01'::date) AND (dta <= '2017-01-31'::date))
  • Filter: ((pdv > '0'::numeric) AND (num > '0'::numeric) AND (hrm >= '0'::numeric) AND (hrm <= '24'::numeric) AND (ope > '0'::numeric))
  • Heap Blocks: exact=53583
11. 222.021 222.021 ↑ 1.4 33,315 9

Bitmap Index Scan on pdvcup00_001 (cost=0.00..1,439.73 rows=47,471 width=0) (actual time=24.669..24.669 rows=33,315 loops=9)

  • Index Cond: ((emp = e0.cod) AND (dta >= '2017-01-01'::date) AND (dta <= '2017-01-31'::date))
12. 260,257.648 260,257.648 ↓ 7.0 7 299,836

Index Scan using pdvcup01_pkey on pdvcup01 p1 (cost=0.57..8.42 rows=1 width=56) (actual time=0.555..0.868 rows=7 loops=299,836)

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

CTE dadospro

14. 3,114.446 400,138.436 ↓ 31.2 15,007 1

GroupAggregate (cost=3,795.89..3,845.19 rows=481 width=288) (actual time=379,045.751..400,138.436 rows=15,007 loops=1)

  • Group Key: itm.empresa, itm.datacupom, itm.nropdv, itm.nrocupom, itm.horaminuto, itm.trs, itm.ope, itm.ven, itm.cmv, itm.cli, itm.mtr, itm.nr_nfcenumero, itm.pro, itm.flg, itm.icm, itm.tbp, p2.dep, p2.sec, p2.grp, p2.seg, p2.ssg, p2.mrc, p2.uni, p2.abc, p2.sis, p2.pcb
15. 32,216.173 397,023.990 ↓ 4,523.9 2,176,015 1

Sort (cost=3,795.89..3,797.09 rows=481 width=288) (actual time=379,045.337..397,023.990 rows=2,176,015 loops=1)

  • Sort Key: itm.empresa, itm.datacupom, itm.nropdv, itm.nrocupom, itm.horaminuto, itm.trs, itm.ope, itm.ven, itm.cmv, itm.cli, itm.mtr, itm.nr_nfcenumero, itm.pro, itm.flg, itm.icm, itm.tbp, p2.dep, p2.sec, p2.grp, p2.seg, p2.ssg, p2.mrc, p2.uni, p2.abc, p2.sis, p2.pcb
  • Sort Method: external merge Disk: 289992kB
16. 1,117.914 364,807.817 ↓ 4,523.9 2,176,015 1

Nested Loop Left Join (cost=0.41..3,774.46 rows=481 width=288) (actual time=313,700.282..364,807.817 rows=2,176,015 loops=1)

17. 331,049.678 331,049.678 ↓ 4,523.9 2,176,015 1

CTE Scan on cabecalho itm (cost=0.00..9.62 rows=481 width=254) (actual time=313,672.862..331,049.678 rows=2,176,015 loops=1)

18. 32,640.225 32,640.225 ↑ 1.0 1 2,176,015

Index Scan using gcepro02_pkey on gcepro02 p2 (cost=0.41..7.82 rows=1 width=40) (actual time=0.014..0.015 rows=1 loops=2,176,015)

  • Index Cond: (cod = itm.pro)