Cris,
In order to improve your performance try
1) vacuum after inserting a significant amount of data
into your table.
2) Build an index on one or more of the columns used in
the WHERE clause when selecting what row to update.
Btree works best if the column has a high cardinality.
I.e. many different values.
Indexes have a performance penalty during inserts but
help during the select.
3) If you have enough RAM increase the number of shared
buffers.
Regards,
Nikolaus Dilger
"Cristina Surroca" wrote:
Hi,
I'm doing a
project and I've have some problems.
My table has (ID,
atr1,..., atn), but there isn't any primary key because
of the specifications,
it's very similar to a Data warehouse, where every
event is stored. It hasn't
any delete, only inserts (more than 10^7) and many
updates.
The thing is that
it takes too much time in execute. (In my case, before
each update I've to
do select to find which row I have to modify, and I
suppose it does a table
scan. To improve it, I've thought to use a Btree for ID
column).
I use JDBC. To
make it works better, I use:
Prepared
Statements,
setAutocommit(false),
( in my case I don't need transactions).
I'm been reading PostgreSQL
mailing list archive to try to discover other kinds
of "tune", or how
to improve it.
I've also read FAQ and I've found
"3.6) How do I tune the data base engine for better
performance".
But in my case,
would you think vacuum and analyze are good options?
Can I do everything else?
Can I also disable the catalog?
Thanks a lot
yours,
Cris..