Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:
> when benchmarking insert , can there be caching effects?
Yes.
> i insert, delete again, and insert again.
> does anything cache the things that i deleted?
Yes.
How long you wait before another attempt could have a significant
effect on timings. There are background processes writing dirty
buffers, checkpointing with fsync to disk, and vacuuming to clean up
those deleted rows. Until vacuum cleans them up, the deleted rows
will still have index entries which might be searched. Competition
with background processes could affect performance. You might have
a table with space already allocated versus needing to ask the OS to
add more space to it. You might be reusing WAL files versus
creating new ones. Etc.
Getting good benchmarks is hard to do. For starters, you need to
decide how many of those things *should* be included in the
benchmark. Then you need to manage things to measure what should be
measured.
-Kevin