Re: possible TODO: read-only tables, select from indexes

You should read the archives of this list; there was a pretty long
thread about this a few months ago. IIRC the consensus after much debate
was that this feature would add benefit in many instances, especially on
large tables where only a small amount of data changes.

Also, I think there is value to supporting read only tables, in addition
to the index visibility info. I also like the idea of being able to have
heap tuples that don't have visibility information, though I'm not sure
how hard that would be to do. But it would certainly be useful to pull
the version bytes out of a 200M row table.

On Fri, Apr 22, 2005 at 07:25:19PM +0300, Hannu Krosing wrote:
> On R, 2005-04-22 at 11:40 -0400, Bruce Momjian wrote:
> > See this TODO:
> >
> > * Allow data to be pulled directly from indexes
> >
> > Currently indexes do not have enough tuple visibility information
> > to allow data to be pulled from the index without also accessing
> > the heap. One way to allow this is to set a bit to index tuples
> > to indicate if a tuple is currently visible to all transactions
> > when the first valid heap lookup happens.
>
> I don't think this is implementable in any reasonably cheap way (i.e.
> this will be a general performance loss).
>
> This has all the downsides of storing full visibility in index tuples,
> except the size.
>
> > This bit would have to be cleared when a heap tuple is expired.
>
> Does "expired" here mean marked for deletion ?
>
> This can be prohibitively pricey for big tables with lots of indexes, as
> marking the tuple means alsn finding and possibly writing to all index
> enytries pointing to this tuple.
>
> > I think this is the direction we should be heading because it has more
> > general usefulness.
>
> OTOH this will probably never be implemented for the same reason that
> full visibility in index tuples will not, whereas my proposition can be
> implemented quite easily (it's just a SMOP).
>
> > ---------------------------------------------------------------------------
> >
> > Hannu Krosing wrote:
> > > Fetching data from just indexes has been discussed on this list several
> > > times before, and it has been told that this can't be done with postgres
> > > thanks to MVCC.
> > >
> > > But this is true only when data is changing. In a data-warehousing
> > > scenario what it is often needed is a possibility for fast querying of
> > > static historical data.
> > >
> > > If table has been VACUUM'ed or REINDEX'ed after last change
> > > (update/delete/insert), then there is 1-1 correspondence between table
> > > and indexes, and thus no need to fetch the tuple from heap for extra
> > > visibility checks.
> > >
> > > What I am proposing is
> > >
> > > 1) possibility to explicitly change table status to READ-ONLY .
> > >
> > > 2) setting a flag CAN_OMIT_HEAP_CHECK after REINDEX TABLE for tables
> > > that are READ-ONLY
> > >
> > > 3) changing postgres planner/executor to make use of this flag, by not
> > > going to heap for tuples on tables where CAN_OMIT_HEAP_CHECK is true.
> > >
> > > --
> > > Hannu Krosing <hannu(at)skype(dot)net>
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faq
> > >
>
> --
> Hannu Krosing <hannu(at)skype(dot)net>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"