[EMAIL PROTECTED] wrote:
> Could someone first please confirm that left joins are really
> this broken in
> postgres. Perhaps it's something in Sergey's setup/build of postgres.
Funny, I get the same result for both queries. Postgres 7.4.3 on
FreeBSD...
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------
Sort (cost=13.05..13.06 rows=3 width=794)
Sort Key: dbmail_messageblks.messageblk_idnr
-> Nested Loop (cost=0.00..13.03 rows=3 width=794)
-> Index Scan using messages_pkey on dbmail_messages
(cost=0.00..3.18 rows=1 width=8)
Index Cond: (message_idnr = 18139437::bigint)
-> Index Scan using messageblks_physmessage_idx on
dbmail_messageblks (cost=0.00..9.79 rows=4 width=802)
Index Cond: (dbmail_messageblks.physmessage_id =
"outer".physmessage_id)
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------------
Sort (cost=13.05..13.06 rows=3 width=794)
Sort Key: dbmail_messageblks.messageblk_idnr
-> Nested Loop (cost=0.00..13.03 rows=3 width=794)
-> Index Scan using messages_pkey on dbmail_messages
(cost=0.00..3.18 rows=1 width=8)
Index Cond: (message_idnr = 18139437::bigint)
-> Index Scan using messageblks_physmessage_idx on
dbmail_messageblks (cost=0.00..9.79 rows=4 width=802)
Index Cond: (dbmail_messageblks.physmessage_id =
"outer".physmessage_id)
(7 rows)
> Left joins are *so* much more readable than normal joins.
Left Joins and Explicit Joins aren't the same thing. A left join will
automatically return any row in the first specified table regardless of
if it has a matching row in the second table. An explicit join will
only return a row if what you're matching on exists in both tables. At
least, that's how I've understood it - it's pretty late though :) There
could be instances where what you got back wasn't exactly the same,
which is why the query planner may be going nuts.
My guess is that your tables may be out of sync, like a record that
exists in dbmail_messageblks that doesn't have matching dbmail_messages
phymessage_id? Although if you're using Postgres that's impossible....
Can you describe your tables for us and send the results? My guess is
that maybe the Indexes somehow got dropped? Or maybe ALTER TABLE didn't
work correctly on 7.3 and caused the indexes to be dropped or unlinked
somehow? (I'm just throwing some ideas out there, esp cause it seems to
work fine on my install).
Hth, but in any case we should try and always use explicit joins... If
we've decided what the best and fastest way to link a table is, then we
save the planner trouble of having to do it (and we save resources as
well). The only reason why the planner is often not the best choice, is
when you just put a query out there and then don't use EXPLAIN or other
tools to see what the most efficient way of linking tables with your db
may be.
>>> Seems to be postgresql problem. This query is used in 3 places: 2
>>> times in dbmsgbuf.c and 1 time in dbsearch.c. I changed them and
>>> till now everything works fine...
>>>
>>> # postgres --version
>>> postgres (PostgreSQL) 7.3.6
>>
>>
>> This query is a good candidate for being replaced then. Strange that
>> PostgreSQL's query planner doesn't do this right...
>>
>> Since the query will also work in MySQL I'll change it (unless
>> anybody has any good reason for not doing it.)
>>
>> Ilja
