explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M9nK : Optimization for: Optimization for: plan #k3S6; plan #oEp

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.111 477.208 ↓ 2.5 10,566 1

GroupAggregate (cost=69,441.86..69,592.40 rows=4,301 width=48) (actual time=471.063..477.208 rows=10,566 loops=1)

  • Group Key: ((((s.annee)::text || ' - '::text) || a.libelle))
2. 54.896 472.097 ↓ 3.9 16,935 1

Sort (cost=69,441.86..69,452.62 rows=4,301 width=40) (actual time=471.052..472.097 rows=16,935 loops=1)

  • Sort Key: ((((s.annee)::text || ' - '::text) || a.libelle))
  • Sort Method: quicksort Memory: 2115kB
3. 8.132 417.201 ↓ 3.9 16,935 1

Hash Join (cost=549.22..69,182.29 rows=4,301 width=40) (actual time=9.443..417.201 rows=16,935 loops=1)

  • Hash Cond: (s.vin_id = v.id)
4. 4.247 399.770 ↓ 3.9 16,935 1

Hash Join (cost=81.58..68,612.50 rows=4,301 width=16) (actual time=0.114..399.770 rows=16,935 loops=1)

  • Hash Cond: (s.contenant_id = c.id)
5. 395.505 395.505 ↓ 3.9 16,935 1

Index Scan using stock_pkey on stock s (cost=0.42..68,520.01 rows=4,301 width=16) (actual time=0.081..395.505 rows=16,935 loops=1)

  • Filter: ((annee)::double precision = (round((random() * '50'::double precision)) + '1950'::double precision))
  • Rows Removed by Filter: 843194
6. 0.007 0.018 ↑ 400.0 3 1

Hash (cost=66.15..66.15 rows=1,200 width=8) (actual time=0.018..0.018 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
7. 0.011 0.011 ↑ 400.0 3 1

Index Scan using contenant_pkey on contenant c (cost=0.15..66.15 rows=1,200 width=8) (actual time=0.007..0.011 rows=3 loops=1)

8. 2.610 9.299 ↑ 1.1 6,058 1

Hash (cost=382.08..382.08 rows=6,845 width=36) (actual time=9.299..9.299 rows=6,058 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 377kB
9. 3.778 6.689 ↑ 1.1 6,058 1

Hash Left Join (cost=29.20..382.08 rows=6,845 width=36) (actual time=0.442..6.689 rows=6,058 loops=1)

  • Hash Cond: (v.appellation_id = a.id)
10. 2.497 2.497 ↑ 1.1 6,058 1

Index Scan using vin_pkey on vin v (cost=0.28..334.96 rows=6,845 width=8) (actual time=0.019..2.497 rows=6,058 loops=1)

11. 0.187 0.414 ↑ 1.0 319 1

Hash (cost=24.93..24.93 rows=319 width=36) (actual time=0.414..0.414 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
12. 0.227 0.227 ↑ 1.0 319 1

Index Scan using appellation_pkey on appellation a (cost=0.15..24.93 rows=319 width=36) (actual time=0.018..0.227 rows=319 loops=1)