explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Avw9

Settings
# exclusive inclusive rows x rows loops node
1. 7,090.813 10,064.051 ↓ 0.0 0 1

Update on vente (cost=135,090.33..154,134.19 rows=1,157 width=213) (actual time=10,064.051..10,064.051 rows=0 loops=1)

  • Buffers: shared hit=6,367,097 read=774 dirtied=11,860 written=10,216
2. 136.200 2,973.238 ↓ 186.9 216,288 1

Nested Loop (cost=135,090.33..154,134.19 rows=1,157 width=213) (actual time=851.461..2,973.238 rows=216,288 loops=1)

  • Buffers: shared hit=1,372,934
3. 220.154 1,539.310 ↓ 186.9 216,288 1

Hash Join (cost=135,089.76..144,464.15 rows=1,157 width=40) (actual time=851.422..1,539.310 rows=216,288 loops=1)

  • Hash Cond: (((a.code)::text = (dvj.code_article)::text) AND ((m.code)::text = (dvj.code_magasin)::text))
  • Buffers: shared hit=75,207
4. 106.319 514.025 ↓ 1.4 216,930 1

Hash Join (cost=1,378.59..9,944.62 rows=153,974 width=40) (actual time=11.995..514.025 rows=216,930 loops=1)

  • Hash Cond: (v.magasin_id = m.id)
  • Buffers: shared hit=22,491
5. 182.690 406.962 ↓ 1.4 216,930 1

Hash Join (cost=1,295.31..9,456.00 rows=153,974 width=37) (actual time=11.240..406.962 rows=216,930 loops=1)

  • Hash Cond: (v.article_id = a.id)
  • Buffers: shared hit=22,441
6. 213.130 213.130 ↓ 1.4 216,930 1

Index Scan using vente_date_idx on vente v (cost=0.57..7,756.99 rows=153,974 width=34) (actual time=0.043..213.130 rows=216,930 loops=1)

  • Index Cond: (date = '2020-07-23'::date)
  • Buffers: shared hit=21,597
7. 4.072 11.142 ↑ 1.0 20,019 1

Hash (cost=1,044.33..1,044.33 rows=20,033 width=19) (actual time=11.141..11.142 rows=20,019 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,351kB
  • Buffers: shared hit=844
8. 7.070 7.070 ↑ 1.0 20,019 1

Seq Scan on article a (cost=0.00..1,044.33 rows=20,033 width=19) (actual time=0.006..7.070 rows=20,019 loops=1)

  • Buffers: shared hit=844
9. 0.266 0.744 ↑ 1.0 1,479 1

Hash (cost=64.79..64.79 rows=1,479 width=19) (actual time=0.744..0.744 rows=1,479 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 97kB
  • Buffers: shared hit=50
10. 0.478 0.478 ↑ 1.0 1,479 1

Seq Scan on magasin m (cost=0.00..64.79 rows=1,479 width=19) (actual time=0.014..0.478 rows=1,479 loops=1)

  • Buffers: shared hit=50
11. 76.404 805.131 ↑ 1.0 216,885 1

Hash (cost=130,371.96..130,371.96 rows=222,614 width=28) (actual time=805.131..805.131 rows=216,885 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,301kB
  • Buffers: shared hit=52,716
12. 728.727 728.727 ↑ 1.0 216,885 1

Seq Scan on derniere_vente_journee dvj (cost=0.00..130,371.96 rows=222,614 width=28) (actual time=473.941..728.727 rows=216,885 loops=1)

  • Filter: (journee = '2020-07-23'::date)
  • Rows Removed by Filter: 5,995,140
  • Buffers: shared hit=52,716
13. 1,297.728 1,297.728 ↑ 1.0 1 216,288

Index Scan using vente_pkey on vente (cost=0.57..8.36 rows=1 width=181) (actual time=0.006..0.006 rows=1 loops=216,288)

  • Index Cond: (id = v.id)
  • Buffers: shared hit=1,297,727
Planning time : 2.135 ms
Execution time : 18,066.680 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint vente_article_fk 2,398.485 ms 216288 0.011 ms
for constraint vente_magasin_fk 2,086.683 ms 216288 0.010 ms
for constraint join_vente_type_offre_fk 13.274 ms 1224 0.011 ms
for constraint vente_canal_vente_fk 1,738.606 ms 216288 0.008 ms
for constraint vente_unite_fk 1,668.695 ms 216288 0.008 ms