I am no expert but this looks like a file system I/O thing. I set hw.ata.wc=1 for a SATA drive and =0 for a SCSI drive in /boot/loader.conf on my FreeBSD systems. That seems to provide some needed tweaking.
We have recently ported our application to the postgres database. For the most part performance has not been an issue; however there is one situation that is a problem and that is the initial read of rows containing BYTEA values that have an average size of 2 kilobytes or greater. For BYTEA values postgres requires as much 3 seconds to read the values from disk into its buffer cache. After the initial read into buffer cache, performance is comparable to other commercial DBMS that we have ported to. As would be expected the commercial DBMS are also slower to display data that is not already in the buffer cache, but the magnitude of difference for postgres for this type of data read from disk as opposed to read from buffer cache is much greater.
We have vacuumed the table and played around with the database initialization parameters in the postgresql.conf. Neither helped with this problem.
Does anyone have any tips on improving the read from disk performance of BYTEA data that is typically 2KB or larger?
