explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N91U

Settings
# exclusive inclusive rows x rows loops node
1. 0.584 6,301.383 ↓ 1.2 14 1

Sort (cost=994,834.04..994,834.07 rows=12 width=228) (actual time=6,301.382..6,301.383 rows=14 loops=1)

  • Sort Key: re.race_id, re.""numPmu"
  • Sort Method: quicksort Memory: 28kB
2. 10.007 6,300.799 ↓ 1.2 14 1

Hash Join (cost=974,858.33..994,833.83 rows=12 width=228) (actual time=6,287.222..6,300.799 rows=14 loops=1)

  • Hash Cond: (re.horse_id = smq.horse_id)
3. 0.009 11.659 ↓ 1.2 14 1

Nested Loop (cost=1.26..18,693.72 rows=12 width=24) (actual time=5.894..11.659 rows=14 loops=1)

4. 0.005 10.712 ↓ 1.2 14 1

Nested Loop (cost=0.84..18,688.36 rows=12 width=20) (actual time=5.368..10.712 rows=14 loops=1)

5. 0.261 0.261 ↑ 1.0 1 1

Index Scan using ""races_raceKey_idx"" on races ra (cost=0.41..8.44 rows=1 width=4) (actual time=0.261..0.261 rows=1 loops=1)

  • Index Cond: ((""raceKey"")::text = '21062020_4_5'::text)
  • Filter: (((""categorieStatut"")::text <> 'ANNULEE'::text) AND ((specialite)::text ~~ 'TROT_%'::text))
6. 10.446 10.446 ↑ 1.4 14 1

Index Scan using registrations_horse_id_race_id_idx on registrations re (cost=0.42..18,679.73 rows=20 width=20) (actual time=5.106..10.446 rows=14 loops=1)

  • Index Cond: (race_id = ra.id)
  • Filter: ((statut)::text <> 'NON_PARTANT'::text)
7. 0.938 0.938 ↑ 1.0 1 14

Index Only Scan using horses_pkey on horses h (cost=0.42..0.45 rows=1 width=4) (actual time=0.067..0.067 rows=1 loops=14)

  • Index Cond: (id = re.horse_id)
  • Heap Fetches: 14
8. 20.403 6,279.133 ↓ 1.3 58,776 1

Hash (cost=973,029.62..973,029.62 rows=43,716 width=212) (actual time=6,279.133..6,279.133 rows=58,776 loops=1)

  • Buckets: 16,384 Batches: 4 Memory Usage: 2,517kB
9. 31.751 6,258.730 ↓ 1.3 58,776 1

Unique (cost=969,057.47..972,592.46 rows=43,716 width=212) (actual time=6,136.206..6,258.730 rows=58,776 loops=1)

10. 53.670 6,226.979 ↑ 1.0 233,250 1

Subquery Scan on smq (cost=969,057.47..972,003.30 rows=235,666 width=212) (actual time=6,136.205..6,226.979 rows=233,250 loops=1)

11. 349.480 6,173.309 ↑ 1.0 233,250 1

Sort (cost=969,057.47..969,646.64 rows=235,666 width=313) (actual time=6,136.202..6,173.309 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, ra_1.date
  • Sort Method: external merge Disk: 58,272kB
12. 220.022 5,823.829 ↑ 1.0 233,250 1

WindowAgg (cost=870,501.23..878,749.54 rows=235,666 width=313) (actual time=5,546.784..5,823.829 rows=233,250 loops=1)

13. 401.949 5,603.807 ↑ 1.0 233,250 1

Sort (cost=870,501.23..871,090.40 rows=235,666 width=305) (actual time=5,546.774..5,603.807 rows=233,250 loops=1)

  • Sort Key: (((ra_1.distance >= (ra_1.distance - 250)) AND (ra_1.distance <= (ra_1.distance + 250)))), re_1.horse_id, ra_1.date DESC
  • Sort Method: external merge Disk: 51,824kB
14. 206.828 5,201.858 ↑ 1.0 233,250 1

WindowAgg (cost=773,561.99..781,810.30 rows=235,666 width=305) (actual time=4,958.175..5,201.858 rows=233,250 loops=1)

15. 375.768 4,995.030 ↑ 1.0 233,250 1

Sort (cost=773,561.99..774,151.16 rows=235,666 width=289) (actual time=4,958.165..4,995.030 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, ra_1.""categorieParticularite"", ra_1.date DESC
  • Sort Method: external merge Disk: 50,064kB
16. 201.952 4,619.262 ↑ 1.0 233,250 1

WindowAgg (cost=679,842.75..688,091.06 rows=235,666 width=289) (actual time=4,380.973..4,619.262 rows=233,250 loops=1)

17. 385.465 4,417.310 ↑ 1.0 233,250 1

Sort (cost=679,842.75..680,431.92 rows=235,666 width=273) (actual time=4,380.966..4,417.310 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, ra_1.specialite, ra_1.date DESC
  • Sort Method: external merge Disk: 47,680kB
18. 189.359 4,031.845 ↑ 1.0 233,250 1

WindowAgg (cost=589,343.51..597,591.82 rows=235,666 width=273) (actual time=3,808.337..4,031.845 rows=233,250 loops=1)

19. 324.634 3,842.486 ↑ 1.0 233,250 1

Sort (cost=589,343.51..589,932.68 rows=235,666 width=257) (actual time=3,808.329..3,842.486 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, ra_1.nature, ra_1.date DESC
  • Sort Method: external merge Disk: 45,648kB
20. 177.664 3,517.852 ↑ 1.0 233,250 1

WindowAgg (cost=502,064.27..510,312.58 rows=235,666 width=257) (actual time=3,307.235..3,517.852 rows=233,250 loops=1)

21. 289.968 3,340.188 ↑ 1.0 233,250 1

Sort (cost=502,064.27..502,653.44 rows=235,666 width=241) (actual time=3,307.228..3,340.188 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, ra_1.corde, ra_1.date DESC
  • Sort Method: external merge Disk: 43,512kB
22. 172.552 3,050.220 ↑ 1.0 233,250 1

WindowAgg (cost=418,012.03..426,260.34 rows=235,666 width=241) (actual time=2,846.293..3,050.220 rows=233,250 loops=1)

23. 279.276 2,877.668 ↑ 1.0 233,250 1

Sort (cost=418,012.03..418,601.20 rows=235,666 width=225) (actual time=2,846.288..2,877.668 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, re_1.trainer_id, ra_1.date DESC
  • Sort Method: external merge Disk: 41,264kB
24. 173.651 2,598.392 ↑ 1.0 233,250 1

WindowAgg (cost=362,957.30..371,205.61 rows=235,666 width=225) (actual time=2,391.584..2,598.392 rows=233,250 loops=1)

25. 285.842 2,424.741 ↑ 1.0 233,250 1

Sort (cost=362,957.30..363,546.46 rows=235,666 width=209) (actual time=2,391.575..2,424.741 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, re_1.track_id, ra_1.date DESC
  • Sort Method: external merge Disk: 40,008kB
26. 180.468 2,138.899 ↑ 1.0 233,250 1

WindowAgg (cost=309,512.56..317,760.87 rows=235,666 width=209) (actual time=1,923.846..2,138.899 rows=233,250 loops=1)

27. 310.092 1,958.431 ↑ 1.0 233,250 1

Sort (cost=309,512.56..310,101.72 rows=235,666 width=193) (actual time=1,923.837..1,958.431 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, re_1.driver_id, ra_1.date DESC
  • Sort Method: external merge Disk: 38,584kB
28. 209.040 1,648.339 ↑ 1.0 233,250 1

WindowAgg (cost=257,681.32..265,929.63 rows=235,666 width=193) (actual time=1,404.578..1,648.339 rows=233,250 loops=1)

29. 256.239 1,439.299 ↑ 1.0 233,250 1

Sort (cost=257,681.32..258,270.48 rows=235,666 width=177) (actual time=1,404.570..1,439.299 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, re_1.oeilleres, ra_1.date DESC
  • Sort Method: external merge Disk: 36,264kB
30. 161.770 1,183.060 ↑ 1.0 233,250 1

WindowAgg (cost=207,460.08..215,708.39 rows=235,666 width=177) (actual time=991.376..1,183.060 rows=233,250 loops=1)

31. 201.686 1,021.290 ↑ 1.0 233,250 1

Sort (cost=207,460.08..208,049.24 rows=235,666 width=161) (actual time=991.369..1,021.290 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, re_1.deferre, ra_1.date DESC
  • Sort Method: external merge Disk: 34,016kB
32. 167.708 819.604 ↑ 1.0 233,250 1

WindowAgg (cost=157,081.34..167,097.15 rows=235,666 width=161) (actual time=608.993..819.604 rows=233,250 loops=1)

33. 199.223 651.896 ↑ 1.0 233,250 1

Sort (cost=157,081.34..157,670.51 rows=235,666 width=113) (actual time=608.986..651.896 rows=233,250 loops=1)

  • Sort Key: re_1.horse_id, ra_1.date DESC
  • Sort Method: external merge Disk: 25,920kB
34. 99.791 452.673 ↑ 1.0 233,250 1

Hash Join (cost=21,076.94..121,551.91 rows=235,666 width=113) (actual time=63.614..452.673 rows=233,250 loops=1)

  • Hash Cond: (re_1.race_id = ra_1.id)
35. 289.315 289.315 ↓ 1.0 641,167 1

Seq Scan on registrations re_1 (cost=0.00..96,446.26 rows=636,921 width=69) (actual time=0.010..289.315 rows=641,167 loops=1)

  • Filter: ((statut)::text <> 'NON_PARTANT'::text)
  • Rows Removed by Filter: 23,701
36. 6.962 63.567 ↑ 1.0 20,007 1

Hash (cost=20,825.26..20,825.26 rows=20,134 width=55) (actual time=63.566..63.567 rows=20,007 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,971kB
37. 56.605 56.605 ↑ 1.0 20,007 1

Seq Scan on races ra_1 (cost=0.00..20,825.26 rows=20,134 width=55) (actual time=0.034..56.605 rows=20,007 loops=1)

  • Filter: (((""categorieStatut"")::text <> 'ANNULEE'::text) AND (date >= '2019-01-01'::date) AND (date <= '2020-06-28'::date))
  • Rows Removed by Filter: 34,420
Planning time : 2.532 ms
Execution time : 6,567.552 ms