Re: Query performance PLEASE HELP
От | Dmitry Tkach |
---|---|
Тема | Re: Query performance PLEASE HELP |
Дата | |
Msg-id | 3E3B0583.1060200@openratings.com обсуждение исходный текст |
Ответ на | Query performance PLEASE HELP (Dmitry Tkach <dmitry@openratings.com>) |
Список | pgsql-general |
Tom Lane wrote: >Dmitry Tkach <dmitry@openratings.com> writes: > >>Sorry, it was the same query as before - just had 'COMP%' instead of >>'POST%': >> > >Oh, I see. How many tradestyle rows actually match "name like 'COMP%'" >and "name like 'POST%'" ? The planner seems to be expecting very few. > > regards, tom lane > Did you receive my last message yet? I said something about it there: There is about 41000 matches for COMP%, about 11000 for POST%, and there are about 14000 entries in the managed_supplier with subscriber =74 So, the query plan performance is, (and, I believe should be) about the same regardless of which table is used for the outer loop. As far as I understand, it is (a little) better to use tradestyle.name index for the POST% query, and it is (a little more) better to use managed_supplier as an outer table for the COMP% query... For some (mysterious) reason, the optimizer chose to do it in exactly the opposite way, but, as I already said a few times earlier - I don't believe it really matters: I have tried forcing it to use one plan or the other for the same query (by setting enable_sort to false and changing the order by clause if necessary) - it DOES NOT MAKE ANY DIFFERENCE (sometimes it is a little quicker then the others, but I have never seen it take less than 5 minutes whatever query plan it uses) - there must be something else going on here that makes it crawl. Dima
В списке pgsql-general по дате отправления: