Search Postgresql Archives

Re: How to make update rapidly?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Feb 20, 4:03 am, heweiwe...@xxxxxxxxx (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...@xxxxxxxxx> 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...@xxxxxxxxx> 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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux