Re: Serious performance problem

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?

Kind regards

Andreas.