agora inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-general@postgresql.org
Subject: Re: Optimizing query?
Date: 3 Feb 2013 05:33:50 GMT
Message-ID: <keksru$o7c$1@gonzo.reversiblemaps.ath.cx> (raw)
References: <CAFj8pRBoXCJYqdObJQax4uUt4BUoqRvWZTpi10kUdNKO9FwsdA@mail.gmail.com>
	<wolfgang-1130131192928.A037318@amadeus3.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>

On 2013-01-31, hamann.w@t-online.de <hamann.w@t-online.de> wrote:
>
> Pavel Stehlule wrote:
>
>>> >> 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 =3D 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) =3D
>>> > tab2.code
>>> >
>
> Hi Pavel, it was just by chance that a fixed size substring would match the
> data at hand. It is more common to have a digit/letter (or vice versa) boundary
> or a hyphen there
>
>>> >
>>> >> both take an enormous time. In the better case that I can subset (e.g. a=
>>> ll 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.
>>>
> I see the problem since obviously every the ~ operator with a non-constant
> pattern is constantly recompiling the pattern.
>
> I wonder whether it would be possible to invent a prefix-match operator that approaches
> the performance of string equality. I noted in the past (not sure whether anything
> has changed in regex matching) that a constant leading part of regex would improve
> performance, i.e. use an index scan to select possible candidates.
>   

you could write a set returning function that opens cursors on both tables using
"ORDER BY code" and merges the results 

-- 
⚂⚃ 100% natural



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

view thread (41+ messages)  latest in thread

Message-ID: <keksru$o7c$1@gonzo.reversiblemaps.ath.cx>
Permalink:  ../keksru$o7c$1@gonzo.reversiblemaps.ath.cx/
Also on:    postgresql.org/message-id/keksru$o7c$1@gonzo.reversiblemaps.ath.cx

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: jasen@xnet.co.nz
  Subject: Re: Optimizing query?
  In-Reply-To: <keksru$o7c$1@gonzo.reversiblemaps.ath.cx>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox