Re: Serious performance problem

Alex Pilosov wrote:
>
> On Tue, 30 Oct 2001, Antonio Fiol [iso-8859-1] Bonnín 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.
>

Maybe just a silly idea, but would'nt it be possible (and useful)
to store tuple validity in a separate bitmap file, that reports in every
bit the validity of the corresponding tuple? It would grow linearly, but
at least it would be very small compared to the actual data...
Best regards
Andrea Aime