explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZyvH

Settings
# exclusive inclusive rows x rows loops node
1. 20,200.727 20,200.727 ↓ 4,134.4 785,539 1

CTE Scan on dadospro (cost=398,396.46..398,400.26 rows=190 width=368) (actual time=14,776.270..20,200.727 rows=785,539 loops=1)

2.          

CTE empresa

3. 0.035 0.035 ↑ 1.0 1 1

Index Only Scan using spsemp00_pkey on spsemp00 (cost=0.14..8.15 rows=1 width=12) (actual time=0.034..0.035 rows=1 loops=1)

  • Index Cond: (cod = '2'::numeric)
  • Heap Fetches: 1
4.          

CTE cabecalho

5. 1,174.622 16,113.185 ↓ 4,134.4 785,539 1

GroupAggregate (cost=396,808.76..396,824.43 rows=190 width=265) (actual time=14,776.178..16,113.185 rows=785,539 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. 12,709.135 14,938.563 ↓ 4,237.6 805,149 1

Sort (cost=396,808.76..396,809.23 rows=190 width=87) (actual time=14,776.128..14,938.563 rows=805,149 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: quicksort Memory: 137801kB
7. 567.599 2,229.428 ↓ 4,237.6 805,149 1

Nested Loop (cost=1.04..396,801.57 rows=190 width=87) (actual time=0.169..2,229.428 rows=805,149 loops=1)

8. 60.993 269.381 ↓ 2.0 87,028 1

Hash Join (cost=0.47..20,391.80 rows=43,786 width=63) (actual time=0.106..269.381 rows=87,028 loops=1)

  • Hash Cond: (p0.emp = e0.cod)
9. 208.345 208.345 ↓ 1.1 323,210 1

Index Scan using pdvcup00_dta_idx on pdvcup00 p0 (cost=0.44..18,804.54 rows=306,499 width=51) (actual time=0.053..208.345 rows=323,210 loops=1)

  • Index Cond: ((dta >= '2017-03-01'::date) AND (dta <= '2017-03-31'::date))
  • Filter: ((pdv > '0'::numeric) AND (hrm >= '0'::numeric) AND (hrm <= 23.59))
10. 0.004 0.043 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.043..0.043 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.039 0.039 ↑ 1.0 1 1

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

12. 1,392.448 1,392.448 ↓ 9.0 9 87,028

Index Scan using pdvcup01_pkey on pdvcup01 p1 (cost=0.57..8.59 rows=1 width=56) (actual time=0.011..0.016 rows=9 loops=87,028)

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

CTE dadospro

14. 946.998 19,462.080 ↓ 4,134.4 785,539 1

Nested Loop Left Join (cost=0.41..1,563.88 rows=190 width=288) (actual time=14,776.266..19,462.080 rows=785,539 loops=1)

15. 16,944.004 16,944.004 ↓ 4,134.4 785,539 1

CTE Scan on cabecalho itm (cost=0.00..3.80 rows=190 width=254) (actual time=14,776.182..16,944.004 rows=785,539 loops=1)

16. 1,571.078 1,571.078 ↑ 1.0 1 785,539

Index Scan using gcepro02_pkey on gcepro02 p2 (cost=0.41..8.20 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=785,539)

  • Index Cond: (cod = itm.pro)