Обсуждение: 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>

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