Re: Need help - optimizer trouble

Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)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