Re: Trying to Tunning DB - Mailing list pgsql-jdbc

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>