Re: Query performance PLEASE HELP

Tom Lane wrote:

>Dmitry Tkach <dmitry(at)openratings(dot)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