explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IEPk : Optimization for: plan #hoYV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 244,934.450 ↓ 2.0 2 1

Subquery Scan on requete2 (cost=1,801,565.48..1,801,565.58 rows=1 width=348) (actual time=244,934.443..244,934.450 rows=2 loops=1)

  • Filter: (requete2.numlig = 1)
  • Execution Time: 245963.872 ms(71 lignes)
2. 0.107 244,934.444 ↓ 2.0 2 1

WindowAgg (cost=1,801,565.48..1,801,565.57 rows=1 width=379) (actual time=244,934.439..244,934.444 rows=2 loops=1)

3. 0.132 244,934.337 ↓ 2.0 2 1

Sort (cost=1,801,565.48..1,801,565.49 rows=1 width=339) (actual time=244,934.337..244,934.337 rows=2 loops=1)

  • Sort Key: piapd.piapd_idisis, piapd.piapd_gtin, (COALESCE(concat(fabricant.piflf_raisoc1, fabricant.piflf_raisoc2), (fabricantsca.piapfb_raisoc)::text)), piapd.piapd_fincde, piaet.pdtrio_idinc, piaet.pdtrio_idttf, piaet.piaet_indpro, pialt.pialt_ucaid, pialt.pialt_prxexp, piaet.piaet_datdeb, piaet.piaet_datfin, piaet.piaet_stavalgt, piapd.piapd_marpar, piapd.piapd_libstd, piapt.pdtrio_id, piapt.piapt_valmin, piapt.piapt_valmax, piael.piael_mnt, pialt.pialt_autcre, pialt.pialt_coutsp, pialt.pialt_ullqte, pialt.pialt_ultqte, pialt.pialt_ultid, pialt.rtrac_datem, pialt.pialt_id, piaslt.rtrac_datem DESC
  • Sort Method: quicksort Memory: 25kB
4. 12.089 244,934.205 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,148,634.09..1,801,565.47 rows=1 width=339) (actual time=219,804.697..244,934.205 rows=2 loops=1)

5. 0.083 244,922.108 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,148,633.94..1,801,561.27 rows=1 width=364) (actual time=219,792.612..244,922.108 rows=2 loops=1)

6. 0.042 244,916.473 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,148,633.66..1,801,555.63 rows=1 width=364) (actual time=219,787.033..244,916.473 rows=2 loops=1)

7. 1,648.403 244,797.913 ↓ 2.0 2 1

Hash Join (cost=1,148,633.24..1,801,550.38 rows=1 width=364) (actual time=219,708.608..244,797.913 rows=2 loops=1)

  • Hash Cond: ((piael.pialt_id = piael2.pialt_id) AND (piael.piael_mnt = (min(piael2.piael_mnt))))
8. 3,446.640 242,485.715 ↓ 9.3 13,458,302 1

Hash Left Join (cost=1,148,620.06..1,793,980.22 rows=1,439,424 width=372) (actual time=175,159.114..242,485.715 rows=13,458,302 loops=1)

  • Hash Cond: (piapd.piflf_idfab = fabricant.piflf_id)
9. 6,602.160 238,985.760 ↓ 9.3 13,458,302 1

Hash Left Join (cost=1,148,113.70..1,789,695.37 rows=1,439,424 width=344) (actual time=175,105.710..238,985.760 rows=13,458,302 loops=1)

  • Hash Cond: (pialt.piaet_id = piaet.piaet_id)
10. 34,335.017 231,190.579 ↓ 9.3 13,458,302 1

Hash Right Join (cost=1,129,497.77..1,767,300.91 rows=1,439,424 width=272) (actual time=173,909.579..231,190.579 rows=13,458,302 loops=1)

  • Hash Cond: (piaslt.pialt_id = pialt.pialt_id)
11. 22,958.802 22,958.802 ↑ 1.0 12,489,520 1

Seq Scan on slec_piaslt piaslt (cost=0.00..389,302.20 rows=12,489,520 width=24) (actual time=8.733..22,958.802 rows=12,489,520 loops=1)

12. 5,179.463 173,896.760 ↓ 9.0 10,766,515 1

Hash (cost=1,073,627.77..1,073,627.77 rows=1,196,880 width=256) (actual time=173,896.760..173,896.760 rows=10,766,515 loops=1)

  • Buckets: 262144 (originally 262144) Batches: 32 (originally 8) Memory Usage: 63489kB
13. 1,444.875 168,717.297 ↓ 9.0 10,766,515 1

Gather (cost=669,649.61..1,073,627.77 rows=1,196,880 width=256) (actual time=138,501.893..168,717.297 rows=10,766,515 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1,191.478 167,272.422 ↓ 7.2 3,588,838 3 / 3

Parallel Hash Left Join (cost=668,649.61..952,939.77 rows=498,700 width=256) (actual time=138,764.852..167,272.422 rows=3,588,838 loops=3)

  • Hash Cond: (piapd.piapd_id = fabricantsca.piapd_id)
15. 28,308.357 165,789.152 ↓ 7.2 3,588,838 3 / 3

Parallel Hash Join (cost=665,655.16..948,053.35 rows=498,700 width=248) (actual time=138,472.495..165,789.152 rows=3,588,838 loops=3)

  • Hash Cond: (piapd.piapd_id = pialt.piapd_id)
16. 21,788.638 21,788.638 ↑ 1.2 1,264,206 3 / 3

Parallel Seq Scan on slec_piapd piapd (cost=0.00..214,319.84 rows=1,579,584 width=104) (actual time=36.660..21,788.638 rows=1,264,206 loops=3)

17. 5,150.554 115,692.157 ↓ 7.2 3,588,838 3 / 3

Parallel Hash (cost=648,706.41..648,706.41 rows=498,700 width=152) (actual time=115,692.157..115,692.157 rows=3,588,838 loops=3)

  • Buckets: 524288 (originally 524288) Batches: 32 (originally 4) Memory Usage: 47168kB
18. 43,002.714 110,541.603 ↓ 7.2 3,588,838 3 / 3

Parallel Hash Join (cost=240,391.92..648,706.41 rows=498,700 width=152) (actual time=70,116.999..110,541.603 rows=3,588,838 loops=3)

  • Hash Cond: (pialt.pialt_id = piael.pialt_id)
19. 56,095.597 56,095.597 ↑ 1.2 3,466,653 3 / 3

Parallel Seq Scan on slec_pialt pialt (cost=0.00..390,009.96 rows=4,327,096 width=102) (actual time=1.333..56,095.597 rows=3,466,653 loops=3)

20. 2,088.930 11,443.292 ↓ 7.2 3,588,838 3 / 3

Parallel Hash (cost=234,158.17..234,158.17 rows=498,700 width=50) (actual time=11,443.291..11,443.292 rows=3,588,838 loops=3)

  • Buckets: 1048576 (originally 2097152) Batches: 16 (originally 1) Memory Usage: 51232kB
21. 1,657.667 9,354.362 ↓ 7.2 3,588,838 3 / 3

Parallel Hash Left Join (cost=9,021.35..234,158.17 rows=498,700 width=50) (actual time=649.604..9,354.362 rows=3,588,838 loops=3)

  • Hash Cond: (piael.piapt_id = piapt.piapt_id)
  • Filter: (COALESCE((piapt.rtrac_etat)::integer, 0) < 8)
22. 7,066.677 7,066.677 ↓ 2.4 3,588,838 3 / 3

Parallel Seq Scan on slec_piael piael (cost=0.00..221,209.49 rows=1,496,100 width=24) (actual time=18.400..7,066.677 rows=3,588,838 loops=3)

  • Filter: (COALESCE((rtrac_etat)::integer, 0) < 8)
  • Rows Removed by Filter: 1801
23. 56.521 630.018 ↑ 1.2 120,368 3 / 3

Parallel Hash (cost=7,140.60..7,140.60 rows=150,460 width=36) (actual time=630.017..630.018 rows=120,368 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 25120kB
24. 573.497 573.497 ↑ 1.2 120,368 3 / 3

Parallel Seq Scan on slec_piapt piapt (cost=0.00..7,140.60 rows=150,460 width=36) (actual time=17.039..573.497 rows=120,368 loops=3)

25. 26.614 291.792 ↓ 1.5 25,649 3 / 3

Parallel Hash (cost=2,786.96..2,786.96 rows=16,599 width=24) (actual time=291.792..291.792 rows=25,649 loops=3)

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 6336kB
26. 265.178 265.178 ↓ 1.5 25,649 3 / 3

Parallel Seq Scan on slec_piapfb fabricantsca (cost=0.00..2,786.96 rows=16,599 width=24) (actual time=6.693..265.178 rows=25,649 loops=3)

  • Filter: (COALESCE((rtrac_etat)::integer, 0) < 8)
  • Rows Removed by Filter: 2569
27. 161.558 1,193.021 ↑ 1.0 327,108 1

Hash (cost=14,527.08..14,527.08 rows=327,108 width=80) (actual time=1,193.021..1,193.021 rows=327,108 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 45015kB
28. 1,031.463 1,031.463 ↑ 1.0 327,108 1

Seq Scan on slec_piaet piaet (cost=0.00..14,527.08 rows=327,108 width=80) (actual time=12.123..1,031.463 rows=327,108 loops=1)

29. 3.446 53.315 ↑ 1.0 11,305 1

Hash (cost=365.05..365.05 rows=11,305 width=44) (actual time=53.315..53.315 rows=11,305 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 799kB
30. 49.869 49.869 ↑ 1.0 11,305 1

Seq Scan on slec_piflf fabricant (cost=0.00..365.05 rows=11,305 width=44) (actual time=13.478..49.869 rows=11,305 loops=1)

31. 0.013 663.795 ↓ 2.0 2 1

Hash (cost=13.16..13.16 rows=1 width=16) (actual time=663.795..663.795 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.024 663.782 ↓ 2.0 2 1

GroupAggregate (cost=0.43..13.15 rows=1 width=16) (actual time=663.776..663.782 rows=2 loops=1)

  • Group Key: piael2.pialt_id
33. 663.758 663.758 ↓ 2.0 2 1

Index Scan using piael_pialt_fk on slec_piael piael2 (cost=0.43..13.14 rows=1 width=16) (actual time=418.515..663.758 rows=2 loops=1)

  • Index Cond: (pialt_id = ANY ('{19100000011411282,19100000012147015}'::bigint[]))
  • Filter: ((rsite_id = 201) AND (COALESCE((rtrac_etat)::integer, 0) < 8))
34. 118.518 118.518 ↑ 1.0 1 2

Index Scan using slec_tfic_pkey on slec_tfic tfic (cost=0.42..5.25 rows=1 width=16) (actual time=59.259..59.259 rows=1 loops=2)

  • Index Cond: (tfic_id = piaslt.tfic_id)
35. 5.552 5.552 ↓ 0.0 0 2

Index Scan using piapet_piaet_fk on slec_piapet piapet (cost=0.28..5.62 rows=2 width=16) (actual time=2.776..2.776 rows=0 loops=2)

  • Index Cond: (piaet_id = piaet.piaet_id)
  • Filter: (rtrac_etat < 9)
36. 0.008 0.008 ↓ 0.0 0 2

Index Scan using slec_ppay_pkey on slec_ppay ppay (cost=0.14..4.17 rows=1 width=11) (actual time=0.004..0.004 rows=0 loops=2)

  • Index Cond: (ppay_id = piapet.ppay_id)
Planning time : 7,749.604 ms