From: | "Gordan Bobic" <gordan(at)freeuk(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Another optimizer question |
Date: | 2001-01-18 17:16:33 |
Message-ID: | 001901c08172$68df66a0$8000000a@localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Zolof | 2001-01-18 17:17:27 | pl/pgSQL & transaction |
Previous Message | Oliver Elphick | 2001-01-18 17:02:52 | Re: nested table |