Re: Trying to Tunning DB

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?
> > > >