Re: Optimizing Queries in OODBs

After takin a swig o' Arrakan spice grog, paul_geoffrey_brown_at_yahoo.com (Paul G. Brown) belched out...:
> Extensible domains adds some complexity, but it also simplifies some
> stuff. Queries over UDTs/UDFs works pretty good in Postgres. Check out the
> Sequoia 2000 and Bucky benchmarks.

One of the bigger challenges with PostgreSQL is that having extensible aggregates (one of the "more advanced" relational features) makes it tough to do aggregate-specific optimizations.

Typical to this, for instance, is that aggregates like MAX() and MIN() can't trivially take advantage of indices. As "aggregates," their values have to be collected by walking across the elements in the set. But it is often the case that you could do the following transform:

  select max(field) from table where [conditions];

    --->

  select field from table where [conditions] order by field descending    limit 1;

To "special case" this is a very dangerous idea, because:

1. It's a "hack" for some aggregates;
2. If other aggregates are needed, it may be counterproductive;
3. If you haven't got a good index for the purpose, "plodding through the aggregate" may not be worse.

The flip side is that by having aggregates be "generic," it doesn't take hideous hacks if you need to add in sophisticated aggregates (common for statistical work).

Of course, this is pretty divergent from the subject; PostgreSQL is certainly NOT an "OODB"; its extensions tend to add to its 'relational fidelity.'

-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "_at_" [name;tld];;
http://cbbrowne.com/info/linuxxian.html
Rules of the  Evil Overlord #128. "I will not  employ robots as agents
of  destruction  if  there  is  any  possible way  that  they  can  be
re-programmed  or if their  battery packs  are externally  mounted and
easily removable." <http://www.eviloverlord.com/>