Re: index organized tables use case

On Dec 12, 2007 9:16 PM, Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> wrote:
> On Wednesday 12 December 2007 10:53, Thomas Kellerer wrote:

> > At least for Oracle it's not mainly the order that improves the
> > performance, but the fact that all the data is kept in the index, so
> > Oracle does not need to go back to the table data after looking up the
> > index entry. There is no "table data" for an index-organized table in
> > Oracle, so only a single lookup is needed.
> >
>
> Yeah, thats a nice feature, and one thats not directly available in Postgres.
> The thing to concentrate on here is the techniques that are available in
> Postgres that might help keep the same schema performant. I think looking at
> partitioning or at partial indexing (making a new index on the code clause)
> could help keep performance with minimal impact to the schema.

And keep in mind, there's no such thing as a free lunch here. Oracle
makes tradeoffs to do this that mean that rolling back a transaction
is MUCH more expensive than it is in pgsql. I'm sure there are other
tradeoffs as well. BTW, apparently, InnoDB does the same type of
thing, and also suffers from the VERY expensive rollback issues as
well.