Donald C. Sumbry ][ wrote:
[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.
Yes, but for me it makes no sense. I would understand, if it will be
SELECT messageblk FROM dbmail_messages LEFT JOIN dbmail_messageblks
USING (physmessage_id)
WHERE dbmail_messages.message_idnr = '%llu'
so that we will get one line for the message which does not have blocks
in message blocks.
But we have
SELECT messageblk FROM dbmail_messageblks LEFT JOIN dbmail_messages
"USING (physmessage_id)
"WHERE dbmail_messages.message_idnr = '%llu'
So, it will add a line for the message block which does not have
according message in dbmail_messages, which will be anyway filtered out
by WHERE. Do not understand how it can be useful.
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....
You are almost right! I have several thousand of messages in
dbmail_messages which do not have according blocks in
dbmail_messageblks. Have no idea for a moment, how this could happen.
Probably during migration I did something wrong. Will have a look now.
Can you describe your tables for us and send the results?
Table descriptions are in my first message in this thread.
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).
I also thought about this, that is why I recreated all indexes.
--
Best regards, Sergey Spiridonov
