I am not sure if this is a bug, an oversight or something else entirely,
but it would appear that if there are two tables, Table1 and Table2, which
are joined using INNER JOIN, specifying WHERE = one of the join fields
doesn't automatically get equalised to the other field.
For example:
SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1)
WHERE Table1.Field1 = 'SomeValue';
takes a very long time (several minutes), and explain says that sequential
scans are used on both tables.
However, changing the above to:
SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1)
WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue';
yields the correct answer in a fraction of a second. Explain says that
indices are being used. However, here's a REALLY strange thing. If I do:
SET ENABLE_SEQSCAN = OFF;
and run the first query, explain says that indices are used, but it STILL
takes forever. The first, slow query executes a merge join, while the
second only executes two index scans in a nested loop.
Why? This seems like a fairly basic thing, but it seems to break something
in the way the query is executed...
Regards.
Gordan
