Re: Performance Issues

John D. Burger wrote:
> Christian Schröder wrote:
>
>> Or would it be possible to tweak how the planner determines the
>> selectivity? I have read in the docs (chapter 54.1) that in case of
>> more than one condition in the where clause, independency is assumed.
>> In my case ("... where test like '11%' and test not like '113%'")
>> this is clearly not the case, so it might be an interesting point to
>> address.
>
> I think the planner does think about the interactions of inequalities,
> so if you can express your query with less-than and friends, or even
> with BETWEEN, you might get a better plan. I don't know the details
> of your setup, but you can do things like this with any ordered type:
>
> where test between '11' and '113'
> or test >= '114'
>
> I know this does not match the exact semantics of your query, but
> hopefully you get the idea.

There are two drawbacks of this solution:

1. It is not always possible to rewrite the "like" or "substring"
queries with standard relational operators.
2. It is annoying for my users that they have to tewak the query
until they find a solution that takes 5 seconds to finish instead
of 4 hours.

I think it is my job as db admin to make the database work the way my
users need it, and not the user's job to find a solution that fits the
database's needs ...

Is there really nothing that I can do?

Regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer