explain.depesz.com

PostgreSQL's explain analyze made readable

Result: noFT

Settings
# exclusive inclusive rows x rows loops node
1. 7,686.145 44,968.312 ↓ 4.2 2,621,881 1

WindowAgg (cost=442,069.15..454,679.93 rows=630,539 width=1,805) (actual time=32,579.237..44,968.312 rows=2,621,881 loops=1)

  • JIT:
  • Functions: 53
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 22.579 ms, Inlining 0.000 ms, Optimization 17.020 ms, Emission 216.308 ms, Total 255.907 ms
2. 23,756.124 37,282.167 ↓ 4.2 2,621,881 1

Sort (cost=442,069.15..443,645.50 rows=630,539 width=1,797) (actual time=32,578.946..37,282.167 rows=2,621,881 loops=1)

  • Sort Key: ("substring"((unnest(ai_stat_pwi.all_odeon)), '/ ([0-9]+)'::text)), ai_stat_pwi.element
  • Sort Method: external merge Disk: 2,829,224kB
3. 4,692.961 13,526.043 ↓ 4.2 2,621,881 1

Hash Left Join (cost=39,395.79..93,520.92 rows=630,539 width=1,797) (actual time=2,991.570..13,526.043 rows=2,621,881 loops=1)

  • Hash Cond: ((ai_stat_pwi.id_grid = mail.id_file) AND (odeon.intervention_id = mail.odeon) AND (ai_pwi.code_projet = mail.pwi_ref_projet) AND (ai_pwi.client = mail.client) AND (ai_pwi.nom_de_la_route = mail.nom_de_la_route))
4. 5,266.274 7,614.476 ↓ 4.2 2,621,881 1

Hash Join (cost=27,384.90..73,234.20 rows=630,539 width=1,769) (actual time=1,772.745..7,614.476 rows=2,621,881 loops=1)

  • Hash Cond: (ai_pwi.idoperation = ai_stat_pwi.element)
5. 575.741 575.741 ↑ 1.0 630,422 1

Seq Scan on ai_pwi (cost=0.00..37,179.39 rows=630,539 width=351) (actual time=0.040..575.741 rows=630,422 loops=1)

6. 17.347 1,772.461 ↑ 4.3 7,073 1

Hash (cost=27,001.02..27,001.02 rows=30,710 width=1,477) (actual time=1,772.460..1,772.461 rows=7,073 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 5,511kB
7. 22.619 1,755.114 ↑ 4.3 7,073 1

Hash Join (cost=25,601.36..27,001.02 rows=30,710 width=1,477) (actual time=1,691.538..1,755.114 rows=7,073 loops=1)

  • Hash Cond: (("substring"((unnest(ai_stat_pwi.all_odeon)), '/ ([0-9]+)'::text)) = odeon.intervention_id)
8. 33.662 278.506 ↑ 4.3 7,073 1

Result (cost=0.00..1,011.94 rows=30,710 width=194) (actual time=235.147..278.506 rows=7,073 loops=1)

9. 242.134 244.844 ↑ 4.3 7,073 1

ProjectSet (cost=0.00..551.29 rows=30,710 width=162) (actual time=235.038..244.844 rows=7,073 loops=1)

10. 2.710 2.710 ↑ 1.0 3,071 1

Seq Scan on ai_stat_pwi (cost=0.00..374.71 rows=3,071 width=275) (actual time=0.018..2.710 rows=3,071 loops=1)

11. 734.755 1,453.989 ↑ 1.0 282,594 1

Hash (cost=22,068.94..22,068.94 rows=282,594 width=1,315) (actual time=1,453.988..1,453.989 rows=282,594 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 153,212kB
12. 719.234 719.234 ↑ 1.0 282,594 1

Seq Scan on odeon_3m_15j odeon (cost=0.00..22,068.94 rows=282,594 width=1,315) (actual time=0.072..719.234 rows=282,594 loops=1)

13. 253.551 1,218.606 ↓ 9.2 223,692 1

Hash (cost=11,462.88..11,462.88 rows=24,356 width=129) (actual time=1,218.605..1,218.606 rows=223,692 loops=1)

  • Buckets: 262,144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 37,656kB
14. 203.116 965.055 ↓ 9.2 223,692 1

Subquery Scan on mail (cost=10,914.88..11,462.88 rows=24,356 width=129) (actual time=505.108..965.055 rows=223,692 loops=1)

15. 618.150 761.939 ↓ 9.2 223,692 1

HashAggregate (cost=10,914.88..11,219.32 rows=24,356 width=129) (actual time=505.102..761.939 rows=223,692 loops=1)

  • Group Key: ai_pwi_fon_mail_detail.id_file, ai_pwi_fon_mail_detail.odeon, ai_pwi_fon_mail_detail.pwi_ref_projet, ai_pwi_fon_mail_detail.client, ai_pwi_fon_mail_detail.nom_de_la_route
16. 143.789 143.789 ↓ 1.0 243,562 1

Seq Scan on ai_pwi_fon_mail_detail (cost=0.00..7,261.55 rows=243,555 width=105) (actual time=0.020..143.789 rows=243,562 loops=1)

Planning time : 3.425 ms
Execution time : 46,731.101 ms