Thread: How to make update rapidly?

Post the table, the query, and the explain output, and then we can help you.

On Feb 19, 2008 7:38 PM, hewei <heweiweihe@gmail.com> wrote:
> Hi,Every body;
>    I have a table contains 100,000 rows, and has a primary key(int).
>   Now ,I need to execute sql command like "update .......... where id=*"(id
> is primary key).
>   I expect execute 1200-1600 sqlcommands per second(1200-1600/s).
>    In test,when the id increase by degrees in sqlcommands, then I can reach
> the speed(1600/s);
>   But in fact , the id  in sqlcommands  is out of rule, then the speed is
> very slow, just 100/s.
>   what can i do? can you help me ?
>

hewei <heweiweihe@gmail.com> writes:
>   id    numeric(5,0)  NOT NULL,

Don't use NUMERIC where INTEGER would do ...

            regards, tom lane

On Feb 19, 2008 9:38 PM, hewei <heweiweihe@gmail.com> wrote:
> Hi,Every body;
>    I have a table contains 100,000 rows, and has a primary key(int).
>   Now ,I need to execute sql command like "update .......... where id=*"(id
> is primary key).
>   I expect execute 1200-1600 sqlcommands per second(1200-1600/s).
>    In test,when the id increase by degrees in sqlcommands, then I can reach
> the speed(1600/s);
>   But in fact , the id  in sqlcommands  is out of rule, then the speed is
> very slow, just 100/s.

Assuming that you're updating a non-indexed field, you should really
look at migrating to 8.3 if you haven't already.  It's performance on
such issues is reportedly much faster than 8.2.

As for processing them in order versus randomly, that's a common
problem.  right sizing shared_buffers so that all of the table can fit
in ram might help too.  As would a caching RAID controller.

On Thu, Feb 21, 2008 at 1:07 AM, hewei <heweiweihe@gmail.com> wrote:
> Hi, Scott Marlowe:
>
> You said that " As for processing them in order versus randomly,that's a
> common problem. "
> do you know why? how postgres work in this scenario.

Pretty much the same way any database would.  it's likely that your
data in the table is in some order.  When you update one row, then the
next n rows are read into memory as well.  Updating these is cheaper
because they don't have to be read, just flushed out to the write
ahead log.  If you have very random access on a table much larger than
your shared_buffers or OS cache, then  it's likely that by the time
you get back to a row on page x it's already been flushed out of the
OS  or pg and has to be fetched again.

On Feb 20, 4:03 am, heweiwe...@gmail.com (hewei) wrote:
> table:
> CREATE TABLE price (
>   TIMESTAMP     Timestamp         NULL,
>   id    numeric(5,0)  NOT NULL,
>   price     numeric(10,3) NULL,
>   primary key (id)
> );
> sql:
> update price set price=* where id=*;
>
> On Feb 20, 2008 11:56 AM, Webb Sprague <webb.spra...@gmail.com> wrote:
>
> > Post the table, the query, and the explain output, and then we can help
> > you.
>
> > On Feb 19, 2008 7:38 PM, hewei <heweiwe...@gmail.com> wrote:
> > > Hi,Every body;
> > >    I have a table contains 100,000 rows, and has a primary key(int).
> > >   Now ,I need to execute sql command like "update .......... where
> > id=*"(id
> > > is primary key).
> > >   I expect execute 1200-1600 sqlcommands per second(1200-1600/s).
> > >    In test,when the id increase by degrees in sqlcommands, then I can
> > reach
> > > the speed(1600/s);
> > >   But in fact , the id  in sqlcommands  is out of rule, then the speed
> > is
> > > very slow, just 100/s.
> > >   what can i do? can you help me ?

You really should only use integer/serial for a primary key or bigint/
bigserial if you have a huge amount of records.  From the manual on
numeric data types:

The type numeric can store numbers with up to 1000 digits of precision
and perform calculations exactly. It is especially recommended for
storing monetary amounts and other quantities where exactness is
required. However, arithmetic on numeric values is very slow compared
to the integer types, or to the floating-point types described in the
next section.

Numerics are (AFAIK) actually stored as strings, and require special
considerations when being worked with.  They are also variable
length.  All of this makes them slow.  unless you have a REALLY good
reason for your primary key to be a numeric, use int or bigint
instead.

On Feb 20, 2008, at 5:03 AM, hewei wrote:

> table:
> CREATE TABLE price (
>   TIMESTAMP     Timestamp         NULL,
>   id    numeric(5,0)  NOT NULL,
>   price     numeric(10,3) NULL,
>   primary key (id)
> );
> sql:
> update price set price=* where id=*;

So you have about 714us on average per query. That's not impossible,
but your hardware and database configuration need to be up to the
task. Updates are generally slower than selects, as they have to find
a spot for the new record, check constraints, write it, etc.

Your problem could be that you're using a prepared statement. For
prepared statements the query plan gets calculated when the prepared
statement is created, without any knowledge of the actual values to
look up. That can result in a non-optimal plan. EXPLAIN ANALYZE of
that query should show more. Re-preparing it after analysing the
table may improve the performance, not sure about that.

Another possible problem, as you're doing updates, is that your data
files get bloated with old rows that don't exist anymore (in your
current transaction). An update is effectively an insert and a delete
(has to be, due to visibility to other transactions - MVCC), so every
update changes one row into two. If you don't vacuum often enough
there will be many more than 100,000 rows to search through.
Added to that; if you don't analyze, the query planner is working
with outdated information and may decide on a bad plan (not a
sequential scan probably, but non-optimal still).

Additionally, if you're trying to update the same row concurrently
from multiple sessions, you're waiting on locks. Not much you can do
about that, not something you're likely to encounter in a real
situation though.

> On Feb 20, 2008 11:56 AM, Webb Sprague <webb.sprague@gmail.com> wrote:
> Post the table, the query, and the explain output, and then we can
> help you.
>
> On Feb 19, 2008 7:38 PM, hewei <heweiweihe@gmail.com> wrote:
> > Hi,Every body;
> >    I have a table contains 100,000 rows, and has a primary key(int).
> >   Now ,I need to execute sql command like "update ..........
> where id=*"(id
> > is primary key).
> >   I expect execute 1200-1600 sqlcommands per second(1200-1600/s).
> >    In test,when the id increase by degrees in sqlcommands, then I
> can reach
> > the speed(1600/s);
> >   But in fact , the id  in sqlcommands  is out of rule, then the
> speed is
> > very slow, just 100/s.
> >   what can i do? can you help me ?
> >
>
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47c15fde233095552171742!