On Wednesday 12 December 2007 10:53, Thomas Kellerer wrote:
> Richard Huxton, 12.12.2007 16:12:
> > Hmm - I'm not sure it does benefit that much. I mean, if you're going to
> > be accessing XXXA, then XXXB, XXXC etc. in order then it clearly helps
> > to have the table with the same order as your primary key. Otherwise,
> > I'd be doubtful you'd see that much benefit.
>
> 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.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
