Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q1TKG-0000Ru-I4 for pgsql-general@arkaria.postgresql.org; Tue, 23 May 2023 14:49:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1q1TKE-00010x-LP for pgsql-general@arkaria.postgresql.org; Tue, 23 May 2023 14:49:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1q1TKE-00010o-8v for pgsql-general@lists.postgresql.org; Tue, 23 May 2023 14:49:22 +0000 Received: from portal-smtp-out.hot.ee ([194.126.101.125]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1q1TKA-001fcd-RC for pgsql-general@postgresql.org; Tue, 23 May 2023 14:49:21 +0000 Received: from localhost (localhost [127.0.0.1]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id 528CA4DAA; Tue, 23 May 2023 17:49:19 +0300 (EEST) X-Virus-Scanned: Debian amavisd-new at vm-hot-relay10.estpak.ee Received: from portal-smtp-out.hot.ee ([127.0.0.1]) by localhost (vm-hot-relay10.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id WW948KppCQJy; Tue, 23 May 2023 17:49:17 +0300 (EEST) Received: from mail.hot.ee (hot-relayhost2.estpak.ee [213.180.2.80]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id 1A9344974; Tue, 23 May 2023 17:49:17 +0300 (EEST) Content-Type: multipart/alternative; boundary="------------jx4zW3Z72yuQnBy75etILIbn" Message-ID: Date: Tue, 23 May 2023 17:49:16 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: How to speed up product code and subcode match Content-Language: et From: Andrus To: Tom Lane , "Bzm@g" Cc: pgsql-general References: <7181fbab-1b51-c005-2576-d287fbcd784c@hot.ee> <908933.1684850425@sss.pgh.pa.us> <21737d42-c902-92f1-2f95-8013abca1531@hot.ee> In-Reply-To: <21737d42-c902-92f1-2f95-8013abca1531@hot.ee> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------jx4zW3Z72yuQnBy75etILIbn Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi! Using index create index on toode ( *split_part( toode, '/',1) *) and query select toode.toode , n2, n3, n4 from toode, vordlusajuhinnak where *split_part( toode.toode, '/',1) *= vordlusajuhinnak.toode; reduces run time to 5 minutes. Andrus. 23.05.2023 17:26 Andrus kirjutas: > > Hi! > >> 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; >> > Can function index > > create index on toode ( *split_part( toode, '/',1) *) > > and query > > select toode.toode , n2, n3, n4 > > from toode, vordlusajuhinnak > > where *split_part( toode.toode, '/',1) *= vordlusajuhinnak.toode; > > > used and keeping existing table structure? Functional index should > produce same speed improvement as using separate column? > > Andrus. > > --------------jx4zW3Z72yuQnBy75etILIbn Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi!

Using index

create index on toode ( split_part( toode, '/',1) )

and query

select toode.toode , n2, n3, n4

from toode, vordlusajuhinnak

where split_part( toode.toode, '/',1) = vordlusajuhinnak.toode;

reduces run time to 5 minutes.

Andrus.


23.05.2023 17:26 Andrus kirjutas:

Hi!

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;

Can function index

create index on toode ( split_part( toode, '/',1) )

and query

select toode.toode , n2, n3, n4

from toode, vordlusajuhinnak

where split_part( toode.toode, '/',1) = vordlusajuhinnak.toode;


used and keeping existing table structure? Functional index should produce same speed improvement as using separate column?

Andrus.


--------------jx4zW3Z72yuQnBy75etILIbn--