Re: Indexed views?

Mischa Sandberg <ischamay(dot)andbergsay(at)activestateway(dot)com> writes:

> I take it that it is a very reasonable assumption that only a small proportion
> of index records are actually invalid (else Yurk why use the index?).

That's faulty logic, the percentage of tuples that are valid is entirely
independent from the percentage of tuples that match your range criterion. Ie,
I could be selecting 100 tuples out of a million -- even if 99 are invalid
it's still worthwhile to use the index.

> Since you're using an index at all, the planner must be expecting a restricted
> set of rows to make it up through to the root. If there is any filter criteria
> against the values from the index rows, you won't even have to check rows for
> tuple visibility, that don't pass that filter.

It's an interesting idea though. But I can't think of many queries where it
would be interesting. The query would still have to visit every page
containing a record used in the final result. So the only time this would be a
significant win is if you're applying very selective restrictions to columns
that were in the index but weren't able to put in the index condition.

This seems like a pretty rare situation; usually the reason you put columns in
an index definition is because it is going to be useful for index conditions--
especially if it's a particularly selective column.

--
greg