Thread: update problem?
Could it be true?? I've a table with < 46000 rows. And a little Update like UPDATE foo set xxx = 'X'; needs about 15 seconds??? What's wrong? Greetings Juergen
On Tue, 9 Jul 2002, Juergen G. Mischke wrote: > Could it be true?? > > I've a table with < 46000 rows. And a little Update like > > UPDATE foo set xxx = 'X'; > > needs about 15 seconds??? > > What's wrong? Does the table have any foreign key constraints or other tables that reference it in foreign key constraints? What's the schema of the table?
Hi, I'm working with Juergen at the same Problem. Consider the following table: > create table test (a char); Now fill the table with some data from test.txt (100.000 rows each row one character) > \copy test from test.txt Now update the table > update test set a='x'; This takes nearly 3 seconds. That would be ok for us. But adding a column and fill it with some data > alter table test add b text; > update test set b='foobar...foobar...foobar... ...'; now the update > update test set a='y'; takes 8 seconds. When I add further columns or increase the amount of data a column holds it gets even worse. Before the column was added explain said: > explain update test set a='x'; > Seq Scan on test2 (cost=0.00..20.00 rows=1000 width=6) (btw: why rows=1000? there are 100.000 rows in the table) And after it was added: > explain update test set a='y'; > Seq Scan on test2 (cost=0.00..20.00 rows=1000 width=38) And for our "real world" table (plain table, no foreign keys) it says: > explain update karte_archiv set ar_k_rechmod='2'; > Seq Scan on karte_archiv (cost=0.00..1609.99 rows=45399 width=359) This update takes over 30 seconds! It uses 7 or 8 logfiles in the pg_xlog directory 16MB each. But does this explain why it take *so* long? Jörg
Hi Stephan,
nothin at all. There're no contraints in the table.
Take a look at the schema :
create table KARTE_ARCHIV
(
AR_KARTENNR VARCHAR(8) not null,
AR_KARTE_OBJEKT VARCHAR(8) not null,
AR_K_ERST_DATUM DATE ,
AR_K_RUECK_DATUM DATE ,
AR_KARTENDRUCKTYP CHAR ,
AR_K_NUMMER VARCHAR(8) ,
AR_K_OBJ_NR VARCHAR(8) ,
AR_K_TOUR VARCHAR(2) ,
AR_K_GEDRUCK DATE ,
AR_K_GERECHNET CHAR ,
AR_K_RECHDAT DATE ,
AR_K_ANZ_POS INTEGER ,
AR_K_ANZ_MIT INTEGER ,
AR_K_BEMERKUNG VARCHAR(200) ,
AR_K_LEISTUNG TEXT ,
AR_RECHGEDRUCKT CHAR ,
AR_K_ERLEDIGT_AM DATE ,
AR_K_KUNDE VARCHAR(60) ,
AR_K_OBJEKT VARCHAR(60) ,
AR_K_RECHMOD CHAR
);
(and some indexes too)
That's all. Any idea??
Hi, There are some ways to create or make table shared across all databases in the cluster? I have created a table in the template1 and I updated his definition in pg_class like in the exemple below: $ psql template1 ..... template1=# CREATE TABLE shared_table (ID SERIAL8, template1=# name VARCHAR UNIQUE); create sequence...... create index ........... CREATE template1=# UPDATE pg_class SET relisshared = TRUE WHERE relname ='shared_table'; UPDATE 1 template1=# insert into shared_table values (1, 'test'); NOTICE: RelationBuildDesc: can't open shared_table: No such file or directory ERROR: cannot open shared_table: No such file or directory NOTICE: RelationBuildDesc: can't open shared_table: No such file or directory ERROR: cannot open shared_table: No such file or directory So, I made some investigation and I realised that shared tables are stored in C:\cygwin\usr\local\pgsql\data\global. But the shared_table I created is stored in the C:\cygwin\usr\local\pgsql\data\base\test. There is some command which allows shared tables creation? Thanks for your help. Mourad.
On Wed, 10 Jul 2002, [iso-8859-1] J�rgen Mischke wrote: > Hi Stephan, > > nothin at all. There're no contraints in the table. > > That's all. Any idea?? Have you vacuumed the table recently? I wonder if you've got a lot of dead rows to ignore.
On Wed, 10 Jul 2002, [ISO-8859-1] J�rg Schulz wrote: > Before the column was added explain said: > > > explain update test set a='x'; > > Seq Scan on test2 (cost=0.00..20.00 rows=1000 width=6) > > (btw: why rows=1000? there are 100.000 rows in the table) Because you haven't vacuum analyzed since loading the data. If you're doing an update of every row of a table, you almost certainly want to at least do a normal vacuum (and possibly a vacuum analyze) of the table afterwards.
>>>>> "JS" == Jörg Schulz <jschulz@sgbs.de> writes: JS> This update takes over 30 seconds! JS> It uses 7 or 8 logfiles in the pg_xlog directory 16MB each. JS> But does this explain why it take *so* long? Try running a vacuum between your updates. Perhaps you're using more disk pages than you can fit into memory, causing lots of disk I/O. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/