Below is an excerpt from a comment over at Reddit. It seemed quite interesting to read about the difference in PG and DB2's query optimizer.
Can anybody shed any more light on this ?
-Sandeep
----------------------
there are a couple really important things that DB2 does with Materialized Query Tables (MQTs, DB2's name for Oracle/everyone else's Materialized Views) that other DBs -- especially free DBs -- don't do:
- Consider the contents MQTs in the query optimizer
- Update MQTs incrementally
for example, imagine you have an MQT backed by this query:
SELECT YEAR(somedate), COUNT(*) FROM sometable GROUP BY YEAR(somedate)
now imagine that, completely independently, you want to run the following query:
SELECT COUNT(*) FROM sometable WHERE YEAR(somedate)=2010
if you're in PostgreSQL (or MySQL, or pretty much any DB but Oracle), then the query optimizer will build a plan that involves a table scan over sometable. for a large table, that sucks. (a clustered index over somedate onsometable can make this less painful, but still. bleh.)
if you're in DB2, the DB2 query optimizer is smart enough to realize that you have that data laying around in your MQT, so it just does a lookup for 2010 in the MQT and returns the corresponding COUNT(*) value. if the MQT has an index over YEAR(somedate), then this is blazing fast. Point DB2. and because the query optimizer considers MQTs out-of-the-box with no changes to existing applications querying the database, if a client's aggregation query is running slow and you can define an MQT that backs that query and a suitable update schedule for the MQT, the client's query will go 10x's faster and require no application changes at all. Another point DB2.
also, DB2 (and Oracle too, pretty sure) lets you update MQTs incrementally. in DB2, when you want to add records to an MQT, you can update the MQT's contents with just those records as opposed to re-running the query that backs it. (for most aggregation functions, anyway.) so, whereas in PostgreSQL you'd have to re-run SELECT COUNT(*) FROM sometable WHERE YEAR(somedate)=2010 to update your MQT if you add records, in DB2 you can just pass those records through the existing MQT and update aggregate values where appropriate. (the syntax for that escapes me at the moment, unfortunately. any DB2-heads lurking about that can help with that?)
anyway, DB2 is excellent at managing enterprise-level data sets. MQTs are an awesome feature it has, and they really change the kinds of applications you can write against the database.
