On Thu, Apr 28, 2005 at 11:40:16AM +1200, Sam Vilain wrote: > Herbert Poetzl wrote: > >>Sadly, Postgres is missing these important features; > >> - bitmap indexes > >> - OLAP query re-writing > >I have absolutely no idea what you are talking about ... > >but: > >New Enterprise Features in 7.4 > >* Hash aggregation in memory to make data warehousing and > > OLAP queries up to 20 times faster; > >(they are now at 8.0.1 or later ...) > > Well, given you asked, and NO THIS ISN'T A FLAMEWAR INVITATION LURKERS :)
hehe, actually I have an oracle question to the expert! how do you do simple 'sequence' types without adding an on insert trigger? but I guess this _is_ already very off-topic, so I'd suggest we continue this thread in private ;) > Bitmap indexes are a simple concept, and last time I checked there were > Pg patches for them (using Pg's pluggable index system), but they weren't > standard. Looking now, all I see is the occasional question on their > mailing list followed by a few clueless replies ('do you mean this...?'). > > All they are is a B-Tree on the distinct *values* of a column, and then > a very long bitmap for each value, one bit for each row in the table, > with 1's in the rows where that value is held. A low CPU overhead > compression scheme makes these fairly efficient. It means that if you're > joining together lots of query conditions on columns with discrete values, > it can be reduced to bitwise operations on these very long bitmaps; on a > modern CPU the actual expanded bitmap might only actually end up in L1, > and the CPU can crank through them at 1.6GHz * 64 * 4 (or however many > ALUs your CPU has); still usually limited by IO capability of course. > > For "data mining" applications, this saves a *lot* of time, sometimes > multiple orders of magnitude. > > And that's still simple. The OLAP query re-writing is even funkier. > > OLAP is a generic term for a large range of technologies, so there > seems to be some things in there labelled for OLAP. There are lots of > tricks that solve the goal of OLAP, no doubt most of which I am ignorant > of. > > But in particular, one thing that Oracle does really nicely is the way > you can make a view "materialized" - ie, the computed view is kept around, > rather than being generated as needed. Then, when you perform queries > on the original table that Oracle figures out could use the computed view > to avoid looking at the original table (or improve speed by using an > index, perhaps), then it transparently re-writes the query to instead use > the materialized view (assuming you know how to flick all the relevant > switches that only advanced Oracle DBAs can reach). > > The upshot of that is that you can take virtually any regularly repeated > query, or hopefully a wide range of common queries, and manually help the > database along by telling it what to pre-calculate. And you don't even > have to 're-run' the queries when the source data changes - it has > support for minimally updating just the bits that changed. > > Oracle certainly has a significant feature lead on Pg for data mining. > > >>Without those, our database would run like cold treacle. > >well, there are a bunch of SQL 'features' not present > >in Oracle either ... so it really depends on the requirements > > Absolutely. I think Oracle stinks as a general purpose application > server back-end. It's buggy as a VW convention, heavy as a lead > elephant and as snappy as old celery for small transactions. thanks for the informative explanations! best, Herbert > > Sam. > _______________________________________________ > Vserver mailing list > Vserver@list.linux-vserver.org > http://list.linux-vserver.org/mailman/listinfo/vserver _______________________________________________ Vserver mailing list Vserver@list.linux-vserver.org http://list.linux-vserver.org/mailman/listinfo/vserver