[Nottingham] TALK: Relational databases, SQL and PostgreSQL.

[Martin Garton]

On Mon, 2007-01-15 at 10:18 +0000, Michael Erskine wrote:
> On Monday 15 January 2007 09:49, Ovid wrote:
> > A cardinal rule of software development:  optimize for *correctness*.
> > Then, and only then, do you optimize for performance.
> 
> Yes, (almost) always true.
> 
> I was thinking of a particular design I'd encountered that had text book 
> correctness and the denormalisation of which solved a number of problems in 
> an expedient manner.

Rather than denormalisation, I have been testing using materialised
query tables (this is a project using db2) to speed up certain queries
and have had good results.  The best part is that you don't always even
need to change the query.  So even though the application is doing a
join query over 4 tables the optimiser spots that it can use the MQT
instead.

I really like this solution because adding the MQT then belongs in the
same class of optimisation as adding an index.  It's non-invasive to the
application.

I believe oracle has something similar that may or not be called
materialised views.

Someone has also written a postgresql plugin to achieve nearly the same
thing, but the optimiser is not aware of it so the application needs to
explicity use the materialised table.

Hmmm. given all this recent discussion on list, my talk on Thursday is
going to seem very basic..

-- 
Martin.