Skip site navigation (1) Skip section navigation (2)

Re: Need help - optimizer trouble

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Need help - optimizer trouble
Date: 2001-04-04 22:27:59
Message-ID: 12783.986423279@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-04-04 23:21:37
Subject: Re: Postgresql.7.0.3
Previous:From: Neil ConwayDate: 2001-04-04 22:25:49
Subject: Re: Need help - optimizer trouble

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group