Re: 9/18 Visual Planner Meeting Wrapup

On Fri, 2008-09-19 at 14:53 -0700, Mark Wong wrote:
> While Selena and Gabrielle were dining with the other speakers at the
> Linux Plumbers Conference, this month's PDXPUG meeting featured Tom
> Raney and his Google Summer of Code project at Portland State
> University under the mentoring of Dr. Len Shapiro. Before getting
> into the details of the Visual Planner, Tom gave a great high level
> overview of PostgreSQL's decision making process for determining how
> to execute a SQL statement. Then he gave brief description of changes
> in the PostgreSQL code that are required in order for the Visual
> Planner to capture all the plans that the database considered
> executing. Using the tool, Tom demonstrated how to browse through all
> the discarded plans to see the cost estimates of each plan indicating
> why those plans were discarded. Hopefully people who will be attended
> the PostgreSQL West Conference 2008 in October will have a chance to
> see Tom repeat his performance.

Looking forward to it!

> Len also posed an interesting question that I will try to repeat accurately:
>
> Can anyone come up with an example of a meaningful query where there
> is a join that does not use an implicit or explicit foreign key?
>

Arbitrary value association is one of the best strengths of the
relational model, and a primary advantage over any hierarchical data
model (like OO or XML data models).

A FK (like any constraint) can only exist from one relation variable to
another relation variable. A join, however, operates on relation
_values_. So, using relational expressions can easily defeat the
usefulness of a FK. For example, let's say you have two tables receiving
sensor data with high-resolution timestamps, "sensor1" and "sensor2".

A useful query might be something like:

SELECT date_trunc('hour', sensor1_ts) as ts, count(*) as sensor1_count
FROM sensor1
NATURAL JOIN
SELECT date_trunc('hour', sensor2_ts) as ts, count(*) as sensor2_count
FROM sensor2

No possible physical design (that is, choice of relation variables and
associated constraints like FKs) could possibly represent the
relationship you're establishing between the sensor readings in the
query. Notice that there's no requirement about the order or nature of
the arriving sensor data, and thus there can be no constraint.

Regards,
Jeff Davis