Re: Serious performance problem

Andrea Aime wrote:
>
> 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...

I see two problems with this approach:

1. Tuple validity is different for different transactions running
concurrently.

We still could cache death-transaction_ids of tuples _in_memory_ quite
cheaply
time-wize, but I'm not sure how big win it will be in general

2. thene is no easy way to know which bit corresponds to which tuple as
each
database page can contain arbitrary number of pages (this one is
easyer,
as we can use a somewhat sparse bitmap that is less space-efficient)

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