Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U0oFF-0007RE-Tq for pgsql-general@arkaria.postgresql.org; Thu, 31 Jan 2013 07:07:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U0oFF-0005kr-ES for pgsql-general@arkaria.postgresql.org; Thu, 31 Jan 2013 07:07:37 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U0oFE-0005km-3t for pgsql-general@postgresql.org; Thu, 31 Jan 2013 07:07:36 +0000 Received: from smtp06.smtpout.orange.fr ([80.12.242.128] helo=smtp.smtpout.orange.fr) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U0oFC-0002c7-4F for pgsql-general@postgresql.org; Thu, 31 Jan 2013 07:07:35 +0000 Received: from [192.168.1.10] ([193.251.178.45]) by mwinf5d29 with ME id uX7X1k0050z9ZVd03X7XUW; Thu, 31 Jan 2013 08:07:31 +0100 Subject: Re: Optimizing query? From: Vincent Veyron To: wolfgang@noten5.maas-noten.de Cc: pgsql-general@postgresql.org In-Reply-To: References: Content-Type: text/plain; charset="ISO-8859-15" Date: Thu, 31 Jan 2013 08:07:30 +0100 Message-ID: <1359616050.2444.3.camel@asus-1001PX.home> Mime-Version: 1.0 X-Mailer: Evolution 2.30.3 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org Le mercredi 30 janvier 2013 à 11:08 +0000, wolfgang@noten5.maas-noten.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. > > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general