Re: test data for query optimizer

nconway(at)klamath(dot)dyndns(dot)org (Neil Conway) writes:
> I'd like to look at the performance of the query optimizer (both the
> traditional one and GEQO) when joining large numbers of tables: 10-15,
> or more. In order to do that (and to get meaningful results), I'll
> need to work with some data that actually requires joins of that
> magnitude.

The easiest way to construct a realistic many-way join is to use a star
schema. Here you have a primary "fact table" that includes a lot of
columns that individually join to the primary keys of other "detail
tables". For example, you might have a column "State" in the fact table
with values like "PA", "NY", etc, and you want to join it to a table
states(abbrev,fullname,...) so your query can display "Pennsylvania",
"New York", etc. It's easy to make up realistic examples that involve
any number of joins.

This is of course only one usage pattern for lots-o-joins, so don't put
too much credence in it alone as a benchmark, but it's certainly a
widely used pattern.

Searching for "star schema" at Google turned up some interesting things
last time I tried it.

regards, tom lane