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..
Hi, Without a primary key, or a key of any kind I don't think anything will help, vacuum analyze helps if there are indexes, you still need to do it; but I don't think it will speed anything up. To find out you can use "explain select ..." Dave On Sat, 2003-04-05 at 07:52, 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.. -- Dave Cramer <Dave@micro-automation.net>
i agree. if there are no keys or indexes you are committing yourself to a full table scan for everything. even if *you* know that your data is inserted in a specific order, without an index the database does not know this and so it has to scan the whole table for every update and select. you need to weigh up the tradeoffs. having no indexes or keys gives you: 1) speedier inserts. 2) smaller footprint. 3) no index maintenance required adding indexes, on the other hand, gives you (assuming you are indexing the right columns): 1) speedier updates 2) speedier queries we have been dealing with this issue where i work, and it really can be a tough call. the thing that gave us the best improvement was bulk loading (outperformed jdbc inserts, non-transactional, by a factor of 100). and adding indexes where needed reduced some 5 minute queries to 5 seconds. the only piece of the puzzle i haven't measured is the overhead of having an index or two on a bulk-loaded table. if someone else has some swags at this i'd appreciated your posting what you have found too. hope this helps. john guthrie ----- Original Message ----- From: "Dave Cramer" <Dave@micro-automation.net> To: "Cristina Surroca" <cris@dmcid.net> Cc: <pgsql-jdbc@postgresql.org> Sent: Saturday, April 05, 2003 8:37 AM Subject: Re: [JDBC] Trying to Tunning DB > Hi, > > Without a primary key, or a key of any kind I don't think anything will > help, vacuum analyze helps if there are indexes, you still need to do > it; but I don't think it will speed anything up. > > To find out you can use "explain select ..." > > Dave > On Sat, 2003-04-05 at 07:52, 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.. > -- > Dave Cramer <Dave@micro-automation.net> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
The thing to do is to drop the index before alot of inserts, and then rebuild it after. Dave On Sat, 2003-04-05 at 09:12, John Guthrie wrote: > i agree. if there are no keys or indexes you are committing yourself to a > full table scan for everything. even if *you* know that your data is > inserted in a specific order, without an index the database does not know > this and so it has to scan the whole table for every update and select. > > you need to weigh up the tradeoffs. having no indexes or keys gives you: > > 1) speedier inserts. > 2) smaller footprint. > 3) no index maintenance required > > adding indexes, on the other hand, gives you (assuming you are indexing the > right columns): > 1) speedier updates > 2) speedier queries > > we have been dealing with this issue where i work, and it really can be a > tough call. the thing that gave us the best improvement was bulk loading > (outperformed jdbc inserts, non-transactional, by a factor of 100). and > adding indexes where needed reduced some 5 minute queries to 5 seconds. > > the only piece of the puzzle i haven't measured is the overhead of having an > index or two on a bulk-loaded table. if someone else has some swags at this > i'd appreciated your posting what you have found too. > > hope this helps. > > john guthrie > > ----- Original Message ----- > From: "Dave Cramer" <Dave@micro-automation.net> > To: "Cristina Surroca" <cris@dmcid.net> > Cc: <pgsql-jdbc@postgresql.org> > Sent: Saturday, April 05, 2003 8:37 AM > Subject: Re: [JDBC] Trying to Tunning DB > > > > Hi, > > > > Without a primary key, or a key of any kind I don't think anything will > > help, vacuum analyze helps if there are indexes, you still need to do > > it; but I don't think it will speed anything up. > > > > To find out you can use "explain select ..." > > > > Dave > > On Sat, 2003-04-05 at 07:52, 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.. > > -- > > Dave Cramer <Dave@micro-automation.net> > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- Dave Cramer <Dave@micro-automation.net>
a similar idea is to create temporary indexes when you need them, if your workflow is amenable to it. --- john guthrie ----- Original Message ----- > The thing to do is to drop the index before alot of inserts, and then > rebuild it after. > > Dave > On Sat, 2003-04-05 at 09:12, John Guthrie wrote: > > i agree. if there are no keys or indexes you are committing yourself to a > > full table scan for everything. even if *you* know that your data is > > inserted in a specific order, without an index the database does not know > > this and so it has to scan the whole table for every update and select. > > > > you need to weigh up the tradeoffs. having no indexes or keys gives you: > > > > 1) speedier inserts. > > 2) smaller footprint. > > 3) no index maintenance required > > > > adding indexes, on the other hand, gives you (assuming you are indexing the > > right columns): > > 1) speedier updates > > 2) speedier queries > > > > we have been dealing with this issue where i work, and it really can be a > > tough call. the thing that gave us the best improvement was bulk loading > > (outperformed jdbc inserts, non-transactional, by a factor of 100). and > > adding indexes where needed reduced some 5 minute queries to 5 seconds. > > > > the only piece of the puzzle i haven't measured is the overhead of having an > > index or two on a bulk-loaded table. if someone else has some swags at this > > i'd appreciated your posting what you have found too. > > > > hope this helps. > > > > john guthrie > > > > > > > Hi, > > > > > > Without a primary key, or a key of any kind I don't think anything will > > > help, vacuum analyze helps if there are indexes, you still need to do > > > it; but I don't think it will speed anything up. > > > > > > To find out you can use "explain select ..." > > > > > > Dave > > > On Sat, 2003-04-05 at 07:52, 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? > > > >