So, I witnessed the following behaviour while using PostgreSQL.
I have a table like this: (id INTEGER ..., msg VARCHAR(2000))
and then I run two programs A
and B
that do the exact same thing,
namely doing 20000 insertions and then 20000 retrievals (based on their id). The only
difference is that program A
does insertions with messages containing
2000 characters while B
just inserts messages containing at most 10 characters.
The thing is that the average time of all the insertions and retrievals
in A
is always about ~15ms less than in B
which doesn't really make sense,
since A
is adding "bigger" data.
Any ideas or hints on why this could be happening? Could it be that when not using
all the characters of the msg
the system uses the rest of the space for other purposes and therefore if msg
is full the system is faster?
Based on @Dan Bracuk comment. I save the time on different events and realized that the following happens, in program A
there quite a few times that insertions are really really fast while in program B
this is never the case and that's why on average A
is faster than B
but I cannot explain this behaviour either.
Some of the tricks we used to speed up SELECT-s in PostgreSQL: LEFT JOIN with redundant conditions, VALUES, extended statistics, primary key type conversion, CLUSTER, pg_hint_plan + bonus.
If you're simply filtering the data and data fits in memory, Postgres is capable of parsing roughly 5-10 million rows per second (assuming some reasonable row size of say 100 bytes). If you're aggregating then you're at about 1-2 million rows per second.
PostgreSQL tries to hold the most frequently accessed data in memory to make performance improvements based on how your queries are performed and the configuration that you give it. But we'll return to memory-based performance optimization later. Separating the application from the database.
I can't reproduce this without more detail about your setup and your programs, so the following is just an educated guess. It's conceivable that your observation is due to TOAST. Once a text field exceeds a certain size, it is stored in a physically separate table. Therefore, the main table is actually smaller than in the case where all the text values are stored inline, and so searches could be faster.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With