explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BXP8

Settings
# exclusive inclusive rows x rows loops node
1. 177,996.928 177,996.928 ↓ 31.2 15,007 1

CTE Scan on dadospro (cost=959,764.55..959,774.17 rows=481 width=368) (actual time=156,936.321..177,996.928 rows=15,007 loops=1)

2.          

CTE empresa

3. 0.076 0.076 ↑ 1.0 9 1

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

  • Heap Fetches: 9
4.          

CTE cabecalho

5. 4,003.920 101,864.505 ↓ 4,523.9 2,176,015 1

GroupAggregate (cost=955,867.40..955,907.08 rows=481 width=265) (actual time=90,609.794..101,864.505 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. 41,514.198 97,860.585 ↓ 4,611.9 2,218,301 1

Sort (cost=955,867.40..955,868.60 rows=481 width=87) (actual time=90,609.776..97,860.585 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. 4,439.893 56,346.387 ↓ 4,611.9 2,218,301 1

Nested Loop (cost=1.01..955,845.97 rows=481 width=87) (actual time=289.535..56,346.387 rows=2,218,301 loops=1)

  • Join Filter: (p0.emp = e0.cod)
  • Rows Removed by Join Filter: 17746408
8. 0.101 0.101 ↑ 1.0 9 1

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

9. 4,617.188 51,906.393 ↓ 4,611.9 2,218,301 9

Materialize (cost=1.01..955,779.66 rows=481 width=77) (actual time=9.912..5,767.377 rows=2,218,301 loops=9)

10. 1,507.302 47,289.205 ↓ 4,611.9 2,218,301 1

Nested Loop (cost=1.01..955,777.25 rows=481 width=77) (actual time=63.550..47,289.205 rows=2,218,301 loops=1)

11. 806.503 806.503 ↓ 2.7 299,836 1

Index Scan using pdvcup00_dta_idx on pdvcup00 p0 (cost=0.44..22,017.97 rows=110,736 width=51) (actual time=42.989..806.503 rows=299,836 loops=1)

  • Index Cond: ((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))
12. 44,975.400 44,975.400 ↓ 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.072..0.150 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,141.190 177,979.927 ↓ 31.2 15,007 1

GroupAggregate (cost=3,795.89..3,845.19 rows=481 width=288) (actual time=156,936.315..177,979.927 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,238.385 174,838.737 ↓ 4,523.9 2,176,015 1

Sort (cost=3,795.89..3,797.09 rows=481 width=288) (actual time=156,935.956..174,838.737 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. 2,802.670 142,600.352 ↓ 4,523.9 2,176,015 1

Nested Loop Left Join (cost=0.41..3,774.46 rows=481 width=288) (actual time=90,631.917..142,600.352 rows=2,176,015 loops=1)

17. 104,981.442 104,981.442 ↓ 4,523.9 2,176,015 1

CTE Scan on cabecalho itm (cost=0.00..9.62 rows=481 width=254) (actual time=90,609.800..104,981.442 rows=2,176,015 loops=1)

18. 34,816.240 34,816.240 ↑ 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.016..0.016 rows=1 loops=2,176,015)

  • Index Cond: (cod = itm.pro)