Re: How to speed up product code and subcode match

Andrus <kobruleht2(at)hot(dot)ee> writes:
> Product table as both main products and subproducts with sizes. Size is
> separated by main product code always by / character:

>     SHOE1-BLACK
>     SHOE1-BLACK/38
>     SHOE1-BLACK/41
>     SHOE1-BLACK/42
>     SHOE1-BLACK/43
>     SHOE2/XXL
>     SHOE2/L

You could probably have devised a worse data representation if
you really tried, but it would have taken some effort. Separate
the product code and size into two columns --- if there's somebody
who really wants to see them in the above format, give them a
view or generated column. Then instead of the impossible-to-optimize
queries you showed, you could do something like

select toode.toode , n2, n3, n4
from toode, vordlusajuhinnak
where toode.toode = vordlusajuhinnak.toode;

Anytime you're trying to join two tables on something that isn't
a plain equality condition (or ANDed conditions), you're in for
a world of hurt.

regards, tom lane