Re: Need help - optimizer trouble

Tom Lane wrote:

>
> 7.1 treats the JOIN clauses as determining join order, where 7.0 did
> not.  Looks like you should re-order the query to be, say,
>
> SELECT doc FROM written_by AS wb
>     JOIN author_keyword AS kw ON kw.author_id=wb.author_id
>     JOIN document ON document.doc_id=wb.doc_id
>     WHERE kw.keyword='foo'
>
> This behavior is a bit of a hack, which we'll likely reconsider in
> future releases, but it does have some redeeming social value too.
> See
> http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html

    Excuse my audacity in posing such a preposterous question... but
shouldn't the SQL syntax be as free of effects on the physical level as
possible?  I've already posted a similar question at pgsql-general that
got no answer, but I will try it again here.

    As I understand from the relational model there should be three levels:
user, logical and physical.  The SELECT command should pertain to the
user level, and the access paths to the physical.  Any alteration in the
behaviour of the optimizer should be or (1) set by the administrator at
the optimizer itself, or (2) at the logical-physical levels' mapping, or
(3) thru hints delivered parallel with, but separate to, the SQL syntax
of the query itself.

    So this change, besides really being a hack, would kind of pollute the
environment by adding complexity to the use of SQL.  Perhaps it would be
cleaner to have somehing like Oracle's hints?

    Thanks for your attention.



--
  _
/ \   Leandro Guimarães Faria Corsetti Dutra         +55 (11) 3040 8913
\ /   Amdocs at Bell Canada                          +1 (514) 786 87 47
  X    Support Center, São Paulo, Brazil          mailto:adbaamd@bell.ca
/ \   http://terravista.pt./Enseada/1989/    mailto:leandrod@amdocs.com