Another optimizer question
От | Gordan Bobic |
---|---|
Тема | Another optimizer question |
Дата | |
Msg-id | 001901c08172$68df66a0$8000000a@localdomain обсуждение исходный текст |
Ответ на | Re: Outer Joins ("Brett W. McCoy" <bmccoy@chapelperilous.net>) |
Ответы |
Re: Another optimizer question
|
Список | 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
В списке pgsql-general по дате отправления: