Re: Need help - optimizer trouble

Helge Bahmann <bahmann@math.tu-freiberg.de> writes:
> SELECT doc FROM document
>     JOIN written_by AS wb ON document.doc_id=wb.doc_id
>     JOIN author_keyword AS kw ON kw.author_id=wb.author_id
>     WHERE kw.keyword='foo'

> 7.0.2 will use the following query plan, query takes ~1 second:
> ...
> 7.1beta4 uses the following query plan, query takes ~150 seconds:
> ...

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

            regards, tom lane