explain.depesz.com

PostgreSQL's explain analyze made readable

Result: flBw

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 10,904.084 ↑ 1.0 25 1

Limit (cost=1,111,332.67..1,111,332.73 rows=25 width=148) (actual time=10,904.071..10,904.084 rows=25 loops=1)

2.          

CTE vm_temp

3. 0.001 0.013 ↓ 0.0 0 1

Unique (cost=65.82..70.52 rows=470 width=20) (actual time=0.013..0.013 rows=0 loops=1)

4. 0.011 0.012 ↓ 0.0 0 1

Sort (cost=65.82..68.17 rows=940 width=20) (actual time=0.012..0.012 rows=0 loops=1)

  • Sort Key: validation_manuelle_temp.oid, validation_manuelle_temp.date_heure, validation_manuelle_temp.vid
  • Sort Method: quicksort Memory: 25kB
5. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on validation_manuelle_temp (cost=0.00..19.40 rows=940 width=20) (actual time=0.001..0.001 rows=0 loops=1)

6. 61.857 10,904.075 ↑ 1,375.9 25 1

Sort (cost=1,111,262.15..1,111,348.15 rows=34,398 width=148) (actual time=10,904.069..10,904.075 rows=25 loops=1)

  • Sort Key: r.date_debut
  • Sort Method: top-N heapsort Memory: 31kB
7. 130.756 10,842.218 ↓ 2.0 68,795 1

Hash Left Join (cost=225,046.66..1,110,291.46 rows=34,398 width=148) (actual time=4,061.460..10,842.218 rows=68,795 loops=1)

  • Hash Cond: (temp.oid = vm_temp.oid)
8. 109.661 10,711.446 ↓ 2.0 68,795 1

Nested Loop (cost=225,031.39..1,107,307.01 rows=34,398 width=140) (actual time=4,061.397..10,711.446 rows=68,795 loops=1)

  • Join Filter: (temp.oid = g.oid)
9. 785.204 10,326.605 ↓ 2.0 68,795 1

Hash Join (cost=225,030.95..821,848.63 rows=34,398 width=133) (actual time=4,061.377..10,326.605 rows=68,795 loops=1)

  • Hash Cond: (o.rid = r.rid)
10. 124.217 6,130.477 ↓ 2.0 68,795 1

Nested Loop (cost=1,889.59..555,324.31 rows=34,398 width=93) (actual time=44.367..6,130.477 rows=68,795 loops=1)

  • Join Filter: (temp.oid = o.oid)
11. 2,516.756 5,662.285 ↓ 2.0 68,795 1

Hash Join (cost=1,889.16..269,865.83 rows=34,398 width=16) (actual time=44.345..5,662.285 rows=68,795 loops=1)

  • Hash Cond: (req_o.oid = temp.oid)
12. 3,101.311 3,101.311 ↑ 1.0 4,855,120 1

Seq Scan on observations req_o (cost=0.00..183,670.25 rows=4,855,125 width=12) (actual time=0.049..3,101.311 rows=4,855,120 loops=1)

13. 21.346 44.218 ↓ 2.0 68,795 1

Hash (cost=1,324.19..1,324.19 rows=34,398 width=4) (actual time=44.218..44.218 rows=68,795 loops=1)

  • Buckets: 4096 Batches: 4 (originally 2) Memory Usage: 1025kB
14. 22.862 22.872 ↓ 2.0 68,795 1

Seq Scan on temp_5705 temp (cost=42.25..1,324.19 rows=34,398 width=4) (actual time=0.028..22.872 rows=68,795 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
15.          

SubPlan (for Seq Scan)

16. 0.007 0.010 ↑ 1,290.0 1 1

HashAggregate (cost=26.12..39.02 rows=1,290 width=4) (actual time=0.010..0.010 rows=1 loops=1)

17. 0.003 0.003 ↑ 1,290.0 1 1

Seq Scan on temp_observations_en_cours (cost=0.00..22.90 rows=1,290 width=4) (actual time=0.003..0.003 rows=1 loops=1)

18. 343.975 343.975 ↑ 1.0 1 68,795

Index Scan using observations_oid_idx on observations o (cost=0.43..8.29 rows=1 width=77) (actual time=0.005..0.005 rows=1 loops=68,795)

  • Index Cond: (oid = req_o.oid)
19. 1,730.717 3,410.924 ↑ 1.0 4,747,927 1

Hash (cost=122,062.27..122,062.27 rows=4,747,927 width=44) (actual time=3,410.924..3,410.924 rows=4,747,927 loops=1)

  • Buckets: 2048 Batches: 512 Memory Usage: 612kB
20. 1,680.207 1,680.207 ↑ 1.0 4,747,927 1

Seq Scan on releves r (cost=0.00..122,062.27 rows=4,747,927 width=44) (actual time=0.006..1,680.207 rows=4,747,927 loops=1)

21. 275.180 275.180 ↑ 1.0 1 68,795

Index Scan using observations_geom_interne_oid_idx on observations_geom_interne g (cost=0.43..8.29 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=68,795)

  • Index Cond: (oid = o.oid)
22. 0.001 0.016 ↓ 0.0 0 1

Hash (cost=9.40..9.40 rows=470 width=8) (actual time=0.016..0.016 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
23. 0.015 0.015 ↓ 0.0 0 1

CTE Scan on vm_temp (cost=0.00..9.40 rows=470 width=8) (actual time=0.015..0.015 rows=0 loops=1)

Total runtime : 10,904.287 ms