Re: Serious performance problem

On Tue, 30 Oct 2001, Antonio Fiol [iso-8859-1] Bonnn wrote:

> > | > Seems that problem is very simple :))
> > | > MSSql can do queries from indexes, without using actual table at all.
> > | > Postgresql doesn't.
> > | >
> > | > So mssql avoids sequental scanning of big table, and simply does scan of
> > | > index which is already in needed order and has very much less size.
<snip>
> > | The consequence for my problem is now: If it is technically possible
> > | to implement index scans without table lookups please implement it. If
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.

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.

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_).

-alex