On 20 Oct 2004 at 14:09, Josh Close wrote:
> On Wed, 20 Oct 2004 19:59:38 +0100, Gary Doades <gpd@gpdnet.co.uk> wrote:
> > Hmm, that seems a bit slow. How big are the rows you are inserting? Have you checked
> > the cpu and IO usage during the inserts? You will need to do some kind of cpu/IO
> > monitoring to determine where the bottleneck is.
>
> The bulk inserts don't take full cpu. Between 40% and 80%. On the
> other hand, a select will take 99% cpu.
Is this the select(1) query? Please post an explain analyze for this and any other "slow"
queries.
I would expect the selects to take 99% cpu if all the data you were trying to select was
already in memory. Is this the case in general? I can do a "select count(1)" on a 500,000
row table in about 1 second on a Athlon 2800+ if all the data is cached. It takes about 25
seconds if it has to fetch it from disk.
I have just done a test by inserting (via COPY) of 149,000 rows in a table with 23
columns, mostly numeric, some int4, 4 timestamps. This took 28 seconds on my
Windows XP desktop, Athlon 2800+, 7200 rpm SATA disk, Postgres 8.0 beta 2. It used
around 20% to 40% cpu during the copy. The only index was the int4 primary key,
nothing else.
How does this compare?
> > What hardware is this on? Sorry if you specified it earlier, I can't seem to find mention of
> > it.
>
> It's on a P4 HT with 1,128 megs ram.
Disk system??
Regards,
Gary.