Re: [GENERAL] How to make update rapidly?
On Feb 20, 2008, at 5:03 AM, hewei wrote: table: CREATE TABLE price ( TIMESTAMP Timestamp NULL, idnumeric(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 <[EMAIL PROTECTED]> wrote: Post the table, the query, and the explain output, and then we can help you. On Feb 19, 2008 7:38 PM, hewei <[EMAIL PROTECTED]> 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! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to make update rapidly?
On Feb 20, 4:03 am, [EMAIL PROTECTED] (hewei) wrote: > table: > CREATE TABLE price ( > TIMESTAMP Timestamp NULL, > idnumeric(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 <[EMAIL PROTECTED]> wrote: > > > Post the table, the query, and the explain output, and then we can help > > you. > > > On Feb 19, 2008 7:38 PM, hewei <[EMAIL PROTECTED]> 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
Re: [GENERAL] How to make update rapidly?
Hi,Scott Marlowe: Following your said: 1.Can i update the postgres's update stragety to that :when update one row ,then load all table rows to memory? 2.If do that, then mean random update 's cost(time) =order update? On Thu, Feb 21, 2008 at 3:23 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Thu, Feb 21, 2008 at 1:07 AM, hewei <[EMAIL PROTECTED]> 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. >
Re: [GENERAL] How to make update rapidly?
On Thu, Feb 21, 2008 at 1:07 AM, hewei <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to make update rapidly?
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. On Wed, Feb 20, 2008 at 3:07 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Feb 19, 2008 9:38 PM, hewei <[EMAIL PROTECTED]> 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. >
Re: [GENERAL] How to make update rapidly?
On Feb 19, 2008 9:38 PM, hewei <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to make update rapidly?
hewei <[EMAIL PROTECTED]> writes: > idnumeric(5,0) NOT NULL, Don't use NUMERIC where INTEGER would do ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to make update rapidly?
table: CREATE TABLE price ( TIMESTAMP Timestamp NULL, idnumeric(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 <[EMAIL PROTECTED]> wrote: > Post the table, the query, and the explain output, and then we can help > you. > > On Feb 19, 2008 7:38 PM, hewei <[EMAIL PROTECTED]> 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 ? > > >
Re: [GENERAL] 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 <[EMAIL PROTECTED]> 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 ? > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] How to make update rapidly?
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 ?
