Re: How to speed up product code and subcode match

Andrus <kobruleht2@hot.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