Re: Seqscan in MAX(index_column)

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> The FAQ does have the example of using ORDER BY LIMIT 1 for MAX(). What
> we don't have a workaround for is COUNT(*). I think that will require
> some cached value that obeys MVCC rules of visibility.

Note that that only handles min()/max() for the whole table. It doesn't handle
the GROUP BY case, for that you need DISTINCT ON with an ORDER BY clause.

I don't see anything special about count(*) that makes it especially amenable
to optimization. In fact I think you're headed to a full updatable
materialized views implementation with this approach.

Materialized views are basically tables that are guaranteed to always contain
the results of a view. They're constructed by executing the specified query
(eg "select bar,count(*) n from foo group by bar"). Then updated every time
the underlying tables are modified (eg "insert into foo (bar) values (1)" does
an "update foo_count_view set n = n+1 where bar=1"). Then they're available
for the optimizer to substitute whenever it sees an expression they can
answer. (so if you do "select count(*) from foo where bar=1" it gets
transformed into "select n from foo_count_view where bar=1").

It's a big project.

I think the min/max optimization is one of those things that "has to happen
sometime". It's something people expect to work, and as long as it doesn't the
database just isn't using the data it already has as well as it could.

Materialized views would be nice, Oracle has them largely because they let the
Oracle techs make a *huge* increase in their spec numbers. They were at the
heart of that challenge a few years ago When Ellison said he would pay a
million dollars to anyone who showed that MSSQL could come within a factor of
10 of Oracle. It was impossible only because Oracle wasn't really doing the
same order of work because of materialized views.

But they're a "would be neat" kind of thing. Nobody comes to the database
expecting to find them. If postgres had them it would be really really cool.
But it seems like there are more important things to be working on.

--
greg