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@xxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
