Bruce Momjian <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
