explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ywdR : prova

Settings
# exclusive inclusive rows x rows loops node
1. 3,003.271 45,133.649 ↓ 20.8 698,879 1

GroupAggregate (cost=1,354,757.13..1,359,547.55 rows=33,617 width=211) (actual time=40,486.078..45,133.649 rows=698,879 loops=1)

  • Group Key: a.descrizione, a.codice_minsan, x.valore_vendita, x.qta_venduta, a.codice_farmacia, a.codice_degrassi, a.codice_fornitore, a.descrizione_forni_gro, a.data
  • Buffers: shared hit=129922 read=471031, temp read=50646 written=51424
2.          

CTE x

3. 1,740.825 20,273.435 ↓ 1.1 148,265 1

GroupAggregate (cost=593,614.41..652,412.05 rows=134,880 width=33) (actual time=12,654.208..20,273.435 rows=148,265 loops=1)

  • Group Key: v.codice_minsan, v.codice_farmacia
  • Buffers: shared hit=42539 read=149747, temp read=29016 written=29016
4. 14,767.367 18,532.610 ↑ 1.0 2,540,919 1

Sort (cost=593,614.41..599,997.61 rows=2,553,282 width=45) (actual time=12,654.184..18,532.61 rows=2,540,919 loops=1)

  • Sort Key: v.codice_minsan, v.codice_farmacia
  • Sort Method: external merge Disk: 153888kB
  • Buffers: shared hit=42539 read=149747, temp read=29016 written=29016
5. 3,765.243 3,765.243 ↑ 1.0 2,540,919 1

Seq Scan on vendite v (cost=0..243,351.64 rows=2,553,282 width=45) (actual time=0.07..3,765.243 rows=2,540,919 loops=1)

  • Filter: ((v.data >= to_date('20180101'::text, 'YYYYMMDD'::text)) AND (v.data < to_date('20200201'::text, 'YYYYMMDD'::text)))
  • Buffers: shared hit=42539 read=149747
6.          

Initplan (for GroupAggregate)

7. 1,090.723 4,766.201 ↑ 1.0 1 1

Aggregate (cost=275,267.67..275,267.68 rows=1 width=8) (actual time=4,766.2..4,766.201 rows=1 loops=1)

  • Buffers: shared hit=42603 read=149683
8. 3,675.478 3,675.478 ↑ 1.0 2,540,919 1

Seq Scan on vendite v_1 (cost=0..243,351.64 rows=2,553,282 width=24) (actual time=0.06..3,675.478 rows=2,540,919 loops=1)

  • Filter: ((v_1.data >= to_date('20180101'::text, 'YYYYMMDD'::text)) AND (v_1.data < to_date('20200201'::text, 'YYYYMMDD'::text)))
  • Buffers: shared hit=42603 read=149683
9. 272.816 3,916.202 ↑ 1.0 1 1

Aggregate (cost=249,734.85..249,734.86 rows=1 width=8) (actual time=3,916.201..3,916.202 rows=1 loops=1)

  • Buffers: shared hit=42571 read=149715
10. 3,643.386 3,643.386 ↑ 1.0 2,540,919 1

Seq Scan on vendite v_2 (cost=0..243,351.64 rows=2,553,282 width=4) (actual time=0.085..3,643.386 rows=2,540,919 loops=1)

  • Filter: ((v_2.data >= to_date('20180101'::text, 'YYYYMMDD'::text)) AND (v_2.data < to_date('20200201'::text, 'YYYYMMDD'::text)))
  • Buffers: shared hit=42571 read=149715
11. 5,086.400 33,447.975 ↓ 20.8 698,879 1

Sort (cost=177,342.55..177,426.59 rows=33,617 width=107) (actual time=31,803.584..33,447.975 rows=698,879 loops=1)

  • Sort Key: a.descrizione, a.codice_minsan, x.valore_vendita, x.qta_venduta, a.codice_farmacia, a.codice_degrassi, a.codice_fornitore, a.descrizione_forni_gro, a.data
  • Sort Method: external merge Disk: 89920kB
  • Buffers: shared hit=44748 read=171633, temp read=50646 written=51424
12. 551.356 28,361.575 ↓ 20.8 698,879 1

Merge Join (cost=167,830.73..174,815.07 rows=33,617 width=107) (actual time=27,564.949..28,361.575 rows=698,879 loops=1)

  • Buffers: shared hit=44748 read=171633, temp read=39383 written=40161
13. 5,243.334 6,501.562 ↑ 1.0 705,821 1

Sort (cost=146,262.46..148,028.92 rows=706,583 width=91) (actual time=6,354.337..6,501.562 rows=705,821 loops=1)

  • Sort Key: a.codice_minsan, a.codice_farmacia
  • Sort Method: external sort Disk: 76704kB
  • Buffers: shared hit=2209 read=21886, temp read=9588 written=9588
14. 1,258.228 1,258.228 ↑ 1.0 705,821 1

Seq Scan on tmp_acquisti_clas_forni a (cost=0..41,387.66 rows=706,583 width=91) (actual time=0.082..1,258.228 rows=705,821 loops=1)

  • Filter: ((a.data >= to_date('20180101'::text, 'YYYYMMDD'::text)) AND (a.data < to_date('20200201'::text, 'YYYYMMDD'::text)))
  • Buffers: shared hit=2209 read=21886
15. 69.529 21,308.657 ↓ 5.3 714,857 1

Materialize (cost=21,568.26..22,242.66 rows=134,880 width=104) (actual time=21,210.601..21,308.657 rows=714,857 loops=1)

  • Buffers: shared hit=42539 read=149747, temp read=29795 written=30573
16. 875.743 21,239.128 ↓ 1.1 148,247 1

Sort (cost=21,568.26..21,905.46 rows=134,880 width=104) (actual time=21,210.593..21,239.128 rows=148,247 loops=1)

  • Sort Key: x.codice_minsan, x.codice_farmacia
  • Sort Method: external sort Disk: 6232kB
  • Buffers: shared hit=42539 read=149747, temp read=29795 written=30573
17. 20,363.385 20,363.385 ↓ 1.1 148,265 1

CTE Scan on x x (cost=0..2,697.6 rows=134,880 width=104) (actual time=12,654.214..20,363.385 rows=148,265 loops=1)

  • Buffers: shared hit=42539 read=149747, temp read=29016 written=29794
Planning time : 1.647 ms
Execution time : 45,230.936 ms