Another optimizer question
От
Gordan Bobic
Тема
Another optimizer question
Дата
Msg-id
001901c08172$68df66a0$8000000a@localdomain
Список
Дерево обсуждения
Re: Outer Joins "Poul L. Christiansen" <poulc@cs.auc.dk>
Re: Outer Joins "Robert B. Easter" <reaster@comptechnews.com>
Re: Outer Joins Tom Lane <tgl@sss.pgh.pa.us>
Re: Outer Joins "Robert B. Easter" <reaster@comptechnews.com>
Re: Outer Joins "Robert B. Easter" <reaster@comptechnews.com>
Re: Outer Joins Tom Lane <tgl@sss.pgh.pa.us>
Re: Outer Joins "Robert B. Easter" <reaster@comptechnews.com>
Re: Outer Joins Tom Lane <tgl@sss.pgh.pa.us>
Re: Outer Joins Bruce Momjian <pgman@candle.pha.pa.us>
Re: Outer Joins "Robert B. Easter" <reaster@comptechnews.com>
Re: Outer Joins Tom Lane <tgl@sss.pgh.pa.us>
Re: Outer Joins Bruce Momjian <pgman@candle.pha.pa.us>
Re: Outer Joins Tom Lane <tgl@sss.pgh.pa.us>
JRelationalFramework Erik Tennant <lists@webpd.com>
Re: Outer Joins "Brett W. McCoy" <bmccoy@chapelperilous.net>
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 по дате отправления