Re: Optimizing query?

2013/1/31 Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr>:
> Le mercredi 30 janvier 2013 à 11:08 +0000, wolfgang(at)noten5(dot)maas-noten(dot)de
> a écrit :
>> Hi,
>>
>> I am trying to match items from 2 tables based on a common string.
>> One is a big table which has one column with entries like XY123, ABC44, etc
>> The table has an index on that column.
>> The second table is, typically, much smaller
>>
>> select .... from tab1, tab2 where tab1.code = tab2.code;
>>
>> This works fine and fast.
>> Now, as a variant, I have some entries like XY423A, XY423B, GF55A, GF55D in the
>> big table and want them to match XY423, GF55 in the second table
>>
>> Variants I have tried
>>
>> select .... from tab1, tab2 where tab1.code ~ (tab2.code||'($|[A-Z])');
>> select .... from tab1, tab2 where tab1.code ~ ('^'||tab2.code||'($|[A-Z])');
>>
>
> Have you tried the substring function?
>
> select .... from tab1, tab2 where substring(tab1.code from 1 for 5) =
> tab2.code
>
>
>> both take an enormous time. In the better case that I can subset (e.g. all candidates in table 2
>> share initial "AX") I get back to manageable times by adding
>> and tab1.code ~ '^AX'
>> into the recipe. Actual runtime with about a million entries in tab1 and 800 entries in tab2
>> is about 40 seconds.

any join where result is related to some function result can be very
slow, because estimation will be out and any repeated function
evaluation is just expensive.

You can try use a functional index.

create index on tab2 ((substring(tab1.code from 1 for 5))

Regards

Pavel Stehule

>>
>> Regards
>> Wolfgang Hamann
>>
>>
>>
>>
>>
>>
>
> --
> Salutations, Vincent Veyron
> http://marica.fr/site/demonstration
> Logiciel de gestion des contentieux juridiques et des sinistres d'assurance
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general