Re: Another optimizer question
От | Gordan Bobic |
---|---|
Тема | Re: Another optimizer question |
Дата | |
Msg-id | 00b001c081f7$fe634e00$8000000a@localdomain обсуждение исходный текст |
Ответ на | Re: Outer Joins ("Brett W. McCoy" <bmccoy@chapelperilous.net>) |
Ответы |
Re: Another optimizer question
|
Список | pgsql-general |
> > SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = > > Table2.Field1) > > WHERE Table1.Field1 = 'SomeValue'; > > [ is slow, but this is fast: ] > > SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = > > Table2.Field1) > > WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue'; > > You're correct, we do not deduce the latter from the former, and so the > first example does not realize that it could use a constrained indexscan > over Table2. > > Maybe we should try to do this, but I suspect we'd waste more cycles > trying than we'd make back on the queries where it helps. Hmm... As far as I can tell, it would help in any query joining several tables on the same field in one table. I don't know how this compares to an "average" case, but it is certainly common in the databases that I'm working on... It would be nice if there was at least an option to enable this sort of behaviour. At the moment, the 6 table joins I am doing are so painfully slow, no matter how I optimize the queries/views that I am going to forget about the whole idea of views and set up some code that will do the separate table subqueries manually. Even with _ALL_ fields that joins are performed on are indexed in hash or btree, it still takes forever. Seen as I have to do this often, unless I can find a way to speed up my queries by an order of magnitude (or several in some cases), I don't see any other way around this. If I do a view that produces the data I want through joins, it takes hours, even with all fields indexed, and after VACUUM ANALYZE. Doing SET ENABLE SEQ_SCAN = OFF doesn't seem to make any difference. The query plan changes, but select times are still roughly the same... Doing the separate subqueries on each table and joining data manualy in the application code takes literaly seconds. I am sure that cannot be right and I must be doing something wrong, so if anyone has a good idea of how to solve this type of problem, I'm not sure I have a lot of options left... Regards. Gordan
В списке pgsql-general по дате отправления: