explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uhPk : Optimization for: plan #rejJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 6,724.378 ↓ 0.0 0 1

Update on transaction_ligne (cost=174,286.17..1,047,843.55 rows=634,881 width=3,261) (actual time=6,724.378..6,724.378 rows=0 loops=1)

  • Buffers: shared hit=353887 read=14155 dirtied=9, temp read=50319 written=49267
2. 2,397.554 6,724.376 ↓ 0.0 0 1

Hash Join (cost=174,286.17..1,047,843.55 rows=634,881 width=3,261) (actual time=6,724.376..6,724.376 rows=0 loops=1)

  • Hash Cond: ((transaction_ligne.trl_pro_codeinterne)::text = (produit.pro_codeinterne)::text)
  • Join Filter: ((transaction_ligne.trl_nmc_serial)::text <> (nomenclature_niveau.nmc_serial)::text)
  • Rows Removed by Join Filter: 639210
  • Buffers: shared hit=353887 read=14155 dirtied=9, temp read=50319 written=49267
3. 2,965.392 3,414.795 ↓ 1.0 639,210 1

Bitmap Heap Scan on transaction_ligne (cost=118,446.95..622,807.48 rows=634,919 width=2,233) (actual time=450.011..3,414.795 rows=639,210 loops=1)

  • Recheck Cond: ((trl_mag_id)::text = '012'::text)
  • Rows Removed by Index Recheck: 2888039
  • Heap Blocks: exact=1814 lossy=313310
  • Buffers: shared hit=339821 read=6995 dirtied=9
4. 449.403 449.403 ↓ 1.0 649,422 1

Bitmap Index Scan on idx_trl_date_retrait (cost=0.00..118,288.22 rows=634,919 width=0) (actual time=449.403..449.403 rows=649,422 loops=1)

  • Index Cond: ((trl_mag_id)::text = '012'::text)
  • Buffers: shared hit=31659 read=33
5. 229.993 912.027 ↑ 1.0 609,441 1

Hash (cost=43,456.36..43,456.36 rows=609,589 width=35) (actual time=912.027..912.027 rows=609,441 loops=1)

  • Buckets: 2048 Batches: 512 Memory Usage: 100kB
  • Buffers: shared hit=14063 read=7160, temp read=2599 written=6636
6. 349.546 682.034 ↑ 1.0 609,441 1

Hash Join (cost=899.46..43,456.36 rows=609,589 width=35) (actual time=18.007..682.034 rows=609,441 loops=1)

  • Hash Cond: (produit.pro_nmc_id = nomenclature_niveau.nmc_id)
  • Buffers: shared hit=14063 read=7160, temp read=2599 written=2569
7. 314.764 314.764 ↑ 1.0 609,589 1

Seq Scan on produit (cost=0.00..26,915.89 rows=609,589 width=18) (actual time=0.025..314.764 rows=609,589 loops=1)

  • Buffers: shared hit=13744 read=7076
8. 6.526 17.724 ↑ 1.0 16,998 1

Hash (cost=569.98..569.98 rows=16,998 width=25) (actual time=17.724..17.724 rows=16,998 loops=1)

  • Buckets: 2048 Batches: 16 Memory Usage: 79kB
  • Buffers: shared hit=316 read=84, temp written=86
9. 11.198 11.198 ↑ 1.0 16,998 1

Seq Scan on nomenclature_niveau (cost=0.00..569.98 rows=16,998 width=25) (actual time=0.004..11.198 rows=16,998 loops=1)

  • Buffers: shared hit=316 read=84