Hi Tom,
This sounds like the same "problem" which prevented PG from using the
indices, and thus giving abyssmal performance in this other thread:
> I have two BIG tables (virtually identical) with 3 NOT NULL columns
> Station_id, TimeObs, Temp_XXXX, with unique indexes on (Station_id,
> TimeObs) and valid ANALYSE (set statistics=100). I want to join the
> two tables with a FULL OUTER JOIN.
>
> When I specify the query as:
>
> SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
> FROM temp_dry_at_2m a
> FULL OUTER JOIN temp_grass b USING (station_id, timeobs)
> WHERE station_id = 52981
> AND timeobs = '2004-1-1 0:0:0'
Then I would also vote for improving the inteligence of the optimizer! :-)
Regards,
Kim.
Tom Lane wrote:
>Phil Endecott <spam_from_postgresql_general@chezphil.org> writes:
>
>
>>I don't see anything in there about LEFT OUTER JOIN though. Any ideas?
>>
>>
>
>Oh, I missed that part of your message. Hmm, I think the issue is that in
>
>
>
>>>D join (M join G on (M.g=G.id)) on (D.id=M.b) where D.id=nnn
>>>
>>>
>
>the planner deduces M.b=nnn by transitivity, but when the join is an
>outer join it can't make the same deduction.
>
>[ thinks some more... ] If we distinguished conditions that hold below
>the join from those that hold above it, we could deduce that M.b=nnn can
>be enforced below the join even though it might not be true above it.
>There's no such mechanism in existence now, though.
>
>A possible workaround is to generate your query like
>
> D left join (M join G on (M.g=G.id)) on (D.id=M.b AND M.b=nnn) where D.id=nnn
>
>but I don't know how practical that is for you.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>