Re: 9/18 Visual Planner Meeting Wrapup

> 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?

Many thanks to Mark for repeating my question accurately and to the
people who provided answers to it. I didn't find the answers to be
satisfactory because I was looking for a query that was useful in a
real life situation.

My interest in the question is that if all useful joins are foreign
key joins then it makes things like optimizer estimates of join
selectivities much simpler. However, there are several useful
non-foreign key joins. They were provided to me by David Maier. I'll
provide the SQL for the first of them, and let you figure out the
rest:

"Who are the congressional candidates running in my district?"

SELECT candname
FROM candidate JOIN resident ON candidate.zip = resident.zip
WHERE resident.name = 'Len Shapiro'

"Which zip codes have both a Burgerville and a White Castle?"

"Who can TA each of the course offerings this year?"
[There can be more than one offering of the same course
number in a year, so course number is only a partial
key]

"Which warehouses stock any of the parts in this order?"

"Which undergrads graduated from the same high school
as which current applicants?"

"What color printers are in my building?"

On Fri, Sep 19, 2008 at 2:53 PM, Mark Wong <markwkm(at)gmail(dot)com> 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.
>
> 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?
>
> There were a couple of new comers and we hope to see them again.
> After the meeting some of us headed to the Lucky Lab for refreshments!
>
> --
> Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pdxpug
>