Another optimizer question

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

In response to

Responses

Browse pgsql-general by date

  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