Skip site navigation (1) Skip section navigation (2)

Re: Performance issue

From: Joseph Bove <jbove(at)vetstar(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance issue
Date: 2003-09-24 17:09:34
Message-ID: 5.1.0.14.2.20030924130825.035a7438@mail.vetstar.com (view raw or flat)
Thread:
Lists: pgsql-performance
Peter,

One possibility is to drop all the indexes, do the insert and re-add the 
indexes.

The more indexes that exist and the more rows that exist, the more costly 
the insert.

Regards,

Joseph

At 05:48 PM 9/24/2003 +1200, peter wrote:
>Hello,
>
>I have been trying to get my Postgres database to do faster inserts.
>
>The environment is basically a single user situation.
>
>The part that I would like to speed up is when a User copys a Project.
>A Project consists of a number of Rooms(say 60). Each room contains a 
>number of items.
>A project will contain say 20,000 records.
>
>Anyway the copying process gets slower and slower, as more projects are 
>added to the database.
>
>My statistics(Athlon 1.8Ghz)
>----------------
>20,000 items            Takes on average 0.078seconds/room
>385,000 items          Takes on average .11seconds/room
>690,000 items          takes on average .270seconds/room
>1,028,000 items       Takes on average .475seconds/room
>
>As can be seen the time taken to process each room increases. A commit 
>occurs when a room has been copied.
>The hard drive  is not being driven very hard. The hard drive light only 
>flashes about twice a second when there are a million records in the database.
>
>I thought that the problem could have been my plpgsql procedure because I 
>assume the code is interpreted.
>However I have just rewriten the code using straight sql(with some temp 
>fields),
>and the times turn out to be almost exactly the same as the plpgsql version.
>
>The read speed for the Application is fine. The sql planner seems to be 
>doing a good job. There has been only one problem
>that I have found with one huge select, which was fixed by a cross join.
>
>  I am running Red hat 8. Some of my conf entries that I have changed follow
>shared_buffers = 3700
>effective_cache_size = 4000
>sort_mem = 32168
>
>Are the increasing times reasonable?
>The times themselves might look slow, but thats because there are a number 
>of tables involved in a Copy
>
>I can increase the shared buffer sizes above 32M, but would this really help?
>
>TIA
>
>peter Mcgregor
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster



In response to

Responses

pgsql-performance by date

Next:From: Matt ClarkDate: 2003-09-24 17:35:34
Subject: Re: Index problem
Previous:From: Richard JonesDate: 2003-09-24 17:05:23
Subject: Re: Performance issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group