explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r9rl

Settings
# exclusive inclusive rows x rows loops node
1. 24.316 758.907 ↑ 1.2 14,378 1

GroupAggregate (cost=29,983.08..31,417.96 rows=16,881 width=2,050) (actual time=722.269..758.907 rows=14,378 loops=1)

  • Group Key: ad.id, ta.amendetype, q2.evenementtype, ((q1.dateavis)::timestamp without time zone), q1.refpv1, (CASE WHEN ((q2.refpv2)::text = (q1.refpv1)::text) THEN NULL::character varying ELSE q2.refpv2 END), st.statut, ti.type_infraction, q1.datenumerisation1, (CASE WHEN (q2.datenumerisation2 = q1.datenumerisation1) THEN NULL::timestamp without time zone ELSE q2.datenumerisation2 END), q1.montant1, (CASE WHEN ((q2.refpv2)::text = (q1.refpv1)::text) THEN NULL::numeric ELSE q2.montant2 END), NULL::numeric(10,2), tdv.devise, q2.libelle_financier, ((q2.dateavis2 + ((q2.delaiavantmajoration)::double precision * '1 day'::interval))), q2.nomreponse, q1.datereponse1, (CASE WHEN ((q2.datenumerisation2 = q1.datenumerisation1) AND ((q2.refpv2)::text = (q1.refpv1)::text) AND (q1.datereponse1 = q2.datereponse2)) THEN NULL::timestamp without time zone ELSE q2.datereponse2 END), q2.nomemetteur, q2.ville, q2.pays, q2.nomtresorerie
2. 118.920 734.591 ↓ 1.0 16,895 1

Sort (cost=29,983.08..30,025.28 rows=16,881 width=2,042) (actual time=722.251..734.591 rows=16,895 loops=1)

  • Sort Key: ad.id, ta.amendetype, q2.evenementtype, ((q1.dateavis)::timestamp without time zone), q1.refpv1, (CASE WHEN ((q2.refpv2)::text = (q1.refpv1)::text) THEN NULL::character varying ELSE q2.refpv2 END), st.statut, ti.type_infraction, q1.datenumerisation1, (CASE WHEN (q2.datenumerisation2 = q1.datenumerisation1) THEN NULL::timestamp without time zone ELSE q2.datenumerisation2 END), q1.montant1, (CASE WHEN ((q2.refpv2)::text = (q1.refpv1)::text) THEN NULL::numeric ELSE q2.montant2 END), tdv.devise, q2.libelle_financier, ((q2.dateavis2 + ((q2.delaiavantmajoration)::double precision * '1 day'::interval))), q2.nomreponse, q1.datereponse1, (CASE WHEN ((q2.datenumerisation2 = q1.datenumerisation1) AND ((q2.refpv2)::text = (q1.refpv1)::text) AND (q1.datereponse1 = q2.datereponse2)) THEN NULL::timestamp without time zone ELSE q2.datereponse2 END), q2.nomemetteur, q2.ville, q2.pays, q2.nomtresorerie
  • Sort Method: external merge Disk: 9928kB
3. 127.524 615.671 ↓ 1.0 16,895 1

Hash Left Join (cost=12,166.93..13,852.77 rows=16,881 width=2,042) (actual time=372.023..615.671 rows=16,895 loops=1)

  • Hash Cond: (ad.deviseid = tdv.id)
4. 13.097 488.099 ↓ 1.0 16,895 1

Hash Join (cost=12,165.86..13,477.19 rows=16,881 width=1,854) (actual time=371.904..488.099 rows=16,895 loops=1)

  • Hash Cond: (ad.typeinfractionid = ti.type_infraction_id)
5. 12.602 474.934 ↓ 1.0 16,895 1

Hash Join (cost=12,164.10..13,266.70 rows=16,881 width=1,640) (actual time=371.822..474.934 rows=16,895 loops=1)

  • Hash Cond: (ad.amendedossierstatutid = st.id)
6. 13.578 462.310 ↓ 1.0 16,895 1

Hash Join (cost=12,162.96..13,077.42 rows=16,881 width=1,240) (actual time=371.787..462.310 rows=16,895 loops=1)

  • Hash Cond: (ad.typeamende = ta.id)
7. 51.988 448.717 ↓ 1.0 16,895 1

Hash Left Join (cost=12,161.92..12,938.16 rows=16,881 width=1,026) (actual time=371.750..448.717 rows=16,895 loops=1)

  • Hash Cond: (ad.id = q2.common_key)
8. 20.292 46.180 ↓ 1.0 16,895 1

Hash Right Join (cost=569.54..1,282.46 rows=16,881 width=83) (actual time=20.813..46.180 rows=16,895 loops=1)

  • Hash Cond: (te.amendedossierid = ad.id)
9. 5.237 5.237 ↓ 1.0 16,894 1

Seq Scan on t_amende_evenement te (cost=0.00..499.81 rows=16,881 width=4) (actual time=0.029..5.237 rows=16,894 loops=1)

10. 11.003 20.651 ↑ 1.0 14,378 1

Hash (cost=389.24..389.24 rows=14,424 width=83) (actual time=20.651..20.651 rows=14,378 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1872kB
11. 9.648 9.648 ↑ 1.0 14,378 1

Seq Scan on t_amende_dossier ad (cost=0.00..389.24 rows=14,424 width=83) (actual time=0.016..9.648 rows=14,378 loops=1)

12. 18.144 350.549 ↓ 14,377.0 14,377 1

Hash (cost=11,592.37..11,592.37 rows=1 width=947) (actual time=350.549..350.549 rows=14,377 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3969kB
13. 22.721 332.405 ↓ 14,377.0 14,377 1

Merge Join (cost=11,591.50..11,592.37 rows=1 width=947) (actual time=299.773..332.405 rows=14,377 loops=1)

  • Merge Cond: (q1.common_key = q2.common_key)
14. 13.568 115.134 ↓ 171.2 14,377 1

Sort (cost=2,240.59..2,240.80 rows=84 width=74) (actual time=114.526..115.134 rows=14,377 loops=1)

  • Sort Key: q1.common_key
  • Sort Method: quicksort Memory: 2405kB
15. 6.251 101.566 ↓ 171.2 14,377 1

Subquery Scan on q1 (cost=2,236.86..2,237.91 rows=84 width=74) (actual time=93.816..101.566 rows=14,377 loops=1)

16. 16.798 95.315 ↓ 171.2 14,377 1

Sort (cost=2,236.86..2,237.07 rows=84 width=78) (actual time=93.813..95.315 rows=14,377 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 2405kB
17. 13.879 78.517 ↓ 171.2 14,377 1

Subquery Scan on t (cost=1,685.12..2,234.17 rows=84 width=78) (actual time=28.575..78.517 rows=14,377 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 2517
18. 34.374 64.638 ↓ 1.0 16,894 1

WindowAgg (cost=1,685.12..2,022.74 rows=16,881 width=62) (actual time=28.564..64.638 rows=16,894 loops=1)

19. 19.799 30.264 ↓ 1.0 16,894 1

Sort (cost=1,685.12..1,727.32 rows=16,881 width=54) (actual time=28.538..30.264 rows=16,894 loops=1)

  • Sort Key: t_amende_evenement.amendedossierid, t_amende_evenement.id
  • Sort Method: quicksort Memory: 2897kB
20. 10.465 10.465 ↓ 1.0 16,894 1

Seq Scan on t_amende_evenement (cost=0.00..499.81 rows=16,881 width=54) (actual time=0.014..10.465 rows=16,894 loops=1)

21. 42.121 194.550 ↓ 171.2 14,377 1

Sort (cost=9,350.91..9,351.12 rows=84 width=835) (actual time=185.229..194.550 rows=14,377 loops=1)

  • Sort Key: q2.common_key
  • Sort Method: external sort Disk: 5000kB
22. 3.787 152.429 ↓ 171.2 14,377 1

Subquery Scan on q2 (cost=9,347.17..9,348.22 rows=84 width=835) (actual time=143.531..152.429 rows=14,377 loops=1)

23. 34.520 148.642 ↓ 171.2 14,377 1

Sort (cost=9,347.17..9,347.38 rows=84 width=839) (actual time=143.527..148.642 rows=14,377 loops=1)

  • Sort Key: t_1.id DESC
  • Sort Method: external merge Disk: 4992kB
24. 2.944 114.122 ↓ 171.2 14,377 1

Subquery Scan on t_1 (cost=8,795.86..9,344.49 rows=84 width=839) (actual time=86.429..114.122 rows=14,377 loops=1)

  • Filter: (t_1.rn = 1)
  • Rows Removed by Filter: 2517
25. 18.801 111.178 ↓ 1.0 16,894 1

WindowAgg (cost=8,795.86..9,133.48 rows=16,881 width=851) (actual time=86.427..111.178 rows=16,894 loops=1)

26. 49.944 92.377 ↓ 1.0 16,894 1

Sort (cost=8,795.86..8,838.06 rows=16,881 width=839) (actual time=86.409..92.377 rows=16,894 loops=1)

  • Sort Key: te_1.amendedossierid, te_1.id DESC
  • Sort Method: external merge Disk: 5816kB
27. 6.336 42.433 ↓ 1.0 16,894 1

Hash Left Join (cost=52.52..1,377.05 rows=16,881 width=839) (actual time=0.778..42.433 rows=16,894 loops=1)

  • Hash Cond: (te_1.tresorerieid = ttr.tresoreries_id)
28. 10.197 36.001 ↓ 1.0 16,894 1

Hash Left Join (cost=47.20..1,158.73 rows=16,881 width=741) (actual time=0.668..36.001 rows=16,894 loops=1)

  • Hash Cond: (te_1.emetteurid = tem.emetteur_id)
29. 8.284 25.308 ↓ 1.0 16,894 1

Hash Left Join (cost=2.68..901.60 rows=16,881 width=512) (actual time=0.156..25.308 rows=16,894 loops=1)

  • Hash Cond: (te_1.destinataireid = tfi.financier_id)
30. 13.266 16.993 ↓ 1.0 16,894 1

Hash Join (cost=1.16..692.68 rows=16,881 width=298) (actual time=0.114..16.993 rows=16,894 loops=1)

  • Hash Cond: (te_1.typeid = ste.id)
31. 3.695 3.695 ↓ 1.0 16,894 1

Seq Scan on t_amende_evenement te_1 (cost=0.00..499.81 rows=16,881 width=84) (actual time=0.059..3.695 rows=16,894 loops=1)

32. 0.013 0.032 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=222) (actual time=0.032..0.032 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.019 0.019 ↑ 1.0 7 1

Seq Scan on t_type_evenement ste (cost=0.00..1.07 rows=7 width=222) (actual time=0.014..0.019 rows=7 loops=1)

34. 0.010 0.031 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=222) (actual time=0.031..0.031 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.021 0.021 ↑ 1.0 23 1

Seq Scan on t_financier tfi (cost=0.00..1.23 rows=23 width=222) (actual time=0.015..0.021 rows=23 loops=1)

36. 0.179 0.496 ↑ 1.0 290 1

Hash (cost=40.90..40.90 rows=290 width=237) (actual time=0.496..0.496 rows=290 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 86kB
37. 0.317 0.317 ↑ 1.0 290 1

Seq Scan on t_emetteur tem (cost=0.00..40.90 rows=290 width=237) (actual time=0.014..0.317 rows=290 loops=1)

38. 0.043 0.096 ↑ 1.0 103 1

Hash (cost=4.03..4.03 rows=103 width=106) (actual time=0.096..0.096 rows=103 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
39. 0.053 0.053 ↑ 1.0 103 1

Seq Scan on t_tresoreries ttr (cost=0.00..4.03 rows=103 width=106) (actual time=0.021..0.053 rows=103 loops=1)

40. 0.005 0.015 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=222) (actual time=0.015..0.015 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.010 0.010 ↑ 1.0 2 1

Seq Scan on t_type_amende ta (cost=0.00..1.02 rows=2 width=222) (actual time=0.009..0.010 rows=2 loops=1)

42. 0.008 0.022 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=408) (actual time=0.022..0.022 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.014 0.014 ↑ 1.0 6 1

Seq Scan on t_statut st (cost=0.00..1.06 rows=6 width=408) (actual time=0.012..0.014 rows=6 loops=1)

44. 0.024 0.068 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=222) (actual time=0.068..0.068 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
45. 0.044 0.044 ↑ 1.0 34 1

Seq Scan on t_type_infraction ti (cost=0.00..1.34 rows=34 width=222) (actual time=0.035..0.044 rows=34 loops=1)

46. 0.009 0.048 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=208) (actual time=0.048..0.048 rows=3 loops=1)

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

Seq Scan on t_devise tdv (cost=0.00..1.03 rows=3 width=208) (actual time=0.037..0.039 rows=3 loops=1)