Re: No heap lookups on index

On 19 Jan 2006 11:25:21 -0500, Greg Stark <gsstark(at)mit(dot)edu> wrote:
>
> Well it seems there were lots of facts posted. Yes you can avoid headaches
> caused by these issues, but we're not really talking about the headaches.

Several were mentioned; some of which could generally be avoided by good
tuning.

We're comparing the performance costs of what are update-in-place and
> non-update-in-place approach.

As PostgreSQL is not an update-in-place system, what is the point in
discussing the costs? How does this solve David's original problem?

There are fundamental costs to non-update-in-place as well. The table sizes
> are bloated by the amount of space used to store older versions and the
> dead
> tuples that haven't been reused yet. Whether this slows down Postgres as
> much
> as having to do a second (or third or fourth) read to a rollback segment
> is a
> valid area for discussion. It's especially interesting to discuss since
> the
> two costs hit different sets of queries unequally.

I agree, but again, we're not talking apples-to-apples. There's far too
many variables to compare Oracle's speed to PostgreSQL's for most types of
operations in the varying types of database deployments.

Well the main difference is the MVCC implementation. Talking about Oracle's
> index implementation while avoiding mentioning the elephant in the room
> would
> be sort of pointless.

I agree that Oracle's MVCC plays *a little* into this index discussion, but
isn't it pointless to discuss the pitfalls of an MVCC implementation that
PostgreSQL does not have? Similarly, how does it solve David's original
question.

Again, I'm fine with discussing these things, but let's keep on topic for
David's sake. He posted a problem that we have discussed many times over.
Let's focus on that problem and give him possible options.

David has stated that the index to heap visibility check is slowing him
down, so what are the possible options:

- Visibility in indexes (-hackers archives cover the pros/cons)
- True organized heaps
- Block level index (Tom/Simon's earlier discussion)