Re: Insert performance

Shridhar,

> Unfortunately he can not use copy due to some constraints.

Why not use COPY to load the table, and then apply the constraints by query
afterwords? It might not be faster, but then again it might.

> I was really amazed to see the numbers. First of all, it beat the sunOS
> machine left and right. Bruce posted some numbers of 9K inserts/sec. Here
> we see the same.
<snip>
> Comments? One thing I can't help to notice is sunOs is not on same scale.
> The sunOS machine is a 1GB RAM machine. It has oracle and mysql running on
> it and have 300MB swap in use but I am sure it has SCSI disk and in all
> respect I would rather expect a RISC machine to perform better than an
> athlon XP machine, at least for an IO.

It's been reported on this list several times that Solaris is the *worst* of
the *nixes for PostgreSQL performance. No analysis has been posted as to
why; my own thoughts are:
- Solaris' multi-threaded architecture which imposes a hefty per-process
overhead, about triple that of Linux, slowing new connections and large
multi-user activity;
- Poor filesystem management; Sun simply hasn't kept up with IBM, Reiser, Red
Hat and BSD in developing filesystems.
... but that's based on inadequate experimentation, just a few tests on
Bonnie++ on a Netra running Solaris 8.

--
Josh Berkus
Aglio Database Solutions
San Francisco