Re: Serious performance problem

"Tille, Andreas" wrote:
>
> On Tue, 30 Oct 2001, Alex Pilosov wrote:
>
> > The feature you are looking for is called 'index coverage'. Unfortunately,
> > it is not easy to implement with Postgresql, and it is one of few
> > outstanding 'nasties'. The reason you can't do it is follows: Postgres
> > uses MVCC, and stores 'when' the tuple is alive inside the tuple. So, even
> > if index contains all the information you need, you still need to access
> > main table to check if the tuple is valid.
> Well, I do not fully understand that stuff, but I get a feeling of the
> problem. Thanks for the explanation.
>
> > Possible workaround: store tuple validity in index, that way, a lot more
> > space is wasted (16 more bytes/tuple/index), and you will need to update
> > all indices when the base table is updated, even if indexed information
> > have not changed.
> This would be acceptable for *my* special application but IŽm afraid
> this could be a problem for others.
>
> > Fundamentally, this may be necessary anyway, to make index handlers aware
> > of transactions and tuple validity (currently, if you have unique index,
> > you may have conflicts when different transactions attempt to insert
> > conflicting data, _at the time of insert, not at time of commit_).
> As I said all this woulnŽt be a problem for my application. I just
> run a sequential insert of data each night. Then the database is read only.
>
> Does anybody see chances that 'index coverage' would be implemented into
> 7.2. This would be a cruxial feature for my application. If it will
> not happen in a reasonable time frame I would have to look for
> alternative database server. Anybody knows something about MySQL or
> Interbase?

If it is static data and simple queries then there is fairly good chance
that MySQL is a good choice .

As fo the other two opensource databases (Interbase and SAPDB (a
modyfied
version of ADABAS released under GPL by SAP - http://www.sapdb.com/) I
have
no direct experience.

I occasionally read sapdb mailing list, and I've got an impression that
it
is quite usable and stable DB once you have set it up. Setting up seems
order(s) of magnitude harder than for PostgreSQL or MySQL.

Weather it actually runs full-table aggregates faster than PG is a thing
I can't comment on, but you could get some of their people to do the
benchmarking for you if you send them an advocacy-urging request, like
I'd
switch if you show me that yur dbis fast enough ;)

-------------------
Hannu