Re: The REAL cost of joins

On 03/03/10 21:59, Marcin Krol wrote:
> What do you think of this? And in general: when (if?) should one
> denormalize data?

As a last resort. No sooner.

The support costs of denormalising your database is such that if you can
reasonably just buy more hardware / add caching / etc, do so.

Oh, and I'm afraid your tests are probably meaningless.
1. The times are too small to be accurate.
2. You have the overhead of starting psql and connecting to the
database, starting a new backend etc.
3. You have the overhead of parsing the query
4. You're fetching all rows (which presumably aren't many) - not a
terribly useful scenario.

If you wanted to measure actual join costs, you'd need to repeat the
tests (say) 100-1000 times in a loop, optionally with prepared plans.
Varying WHERE clauses might be useful too, if that's how your real
application will work.

--
   Richard Huxton
   Archonet Ltd