Thread: Trying to Tunning DB
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..
On Sat, 5 Apr 2003 16:15:37 +0200, "Cristina Surroca" <cris@dmcid.net> wrote: > It hasn't any delete, only inserts (more than 10^7) and many updates. 10^7 inserts per day? If it has updates, it implicitly has deletes. How *many* updates? > The thing is that it takes too much time in execute. We can help you better, if you show us some actual SQL. > b.. setAutocommit(false), ( in my case I don't need transactions). There is no I_dont_need_transactions mode in Postgres. You always have transactions. Either you control them or Postgres performs each statement in its own transaction. As far as I understand, autocommit off means that you don't have to say BEGIN to start a transaction and the transaction stays active until you COMMIT. Your application or JDBC might do this automatically. > But in my case, would you think vacuum and analyze are good options? Definitely! > Can I also disable the catalog? I don't know what you mean, but I am pretty sure, you can't. Servus Manfred