Re: Trying to Tunning DB

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
>