Skip site navigation (1) Skip section navigation (2)

Re: 9/18 Visual Planner Meeting Wrapup

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Mark Wong <markwkm(at)gmail(dot)com>
Cc: Postgresql PDX_Users <pdxpug(at)postgresql(dot)org>
Subject: Re: 9/18 Visual Planner Meeting Wrapup
Date: 2008-09-19 23:49:01
Message-ID: 1221868141.6194.351.camel@dell.linuxdev.us.dell.com (view raw or flat)
Thread:
Lists: pdxpug
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 



In response to

pdxpug by date

Next:From: Josh BerkusDate: 2008-09-22 15:55:19
Subject: PostgreSQL 8.3.4, 8.2.10, etc. Update Release
Previous:From: jterwillDate: 2008-09-19 22:55:36
Subject: Re: 9/18 Visual Planner Meeting Wrapup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group