Re: Equivalent queries and the planner

On Fri, 2005-14-10 at 09:43 -0400, John D. Burger wrote:
> I believe these queries are exactly equivalent, but I presume the
> planner doesn't know that.

> explain select gazPlaceID from gazPlaces
>     where gazPlaceID not in (select gazPlaceID from gazContainers);

> explain select gazPlaceID from gazPlaces
>     except select gazPlaceID from gazContainers;

Yeah, query optimization for set operations is currently quite
primitive; the above transformation is not yet implemented.

> In general, there are lots of ways to express the same abstract
> information need in SQL, and I assumed that there were some set of
> (probably incomplete) equivalencies encoded somewhere.  Is this so?

I don't know of a canonical list of planner transformations. There are
some presentations on planner internals that touch on this, which is
better than nothing:

http://neilc.treehou.se/optimizer.pdf
http://conferences.oreillynet.com/presentations/os2003/lane_tom.pdf

-Neil