From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Alexandru Coseru <alexandru.coseru@totaltelecom.ro>
Cc: Dave Dutcher <dave@tridecap.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Regex performance issue
Date: Sat, 02 Dec 2006 22:04:06 +0000
Message-ID: <4571F856.7090808@enterprisedb.com> (raw)
In-Reply-To: <01ea01c71653$438254e0$3cb16956@alex>
References: <030b01c71651$984577c0$8300a8c0@tridecap.com>
<01ea01c71653$438254e0$3cb16956@alex>
Alexandru Coseru wrote:
> I cannot use LIKE , because the order of the match is reversed.
> The prefix column is containing telephone destinations.
> IE: ^001 - US , ^0039 Italy , etc..
Maybe you could create a functional index on substr(<minimum length of
prefix>)? It might restrict the result set prior to applying the regex
just enough to make the performance acceptable.
> asterisk=> select * from destlist LIMIT 10;
> id | id_ent | dir | prefix | country | network | tip
> ----+--------+-----+------------+-------------+--------------------+-----
> 1 | -1 | 0 | (^0093) | AFGHANISTAN | AFGHANISTAN | 6
> 2 | -1 | 0 | (^00937) | AFGHANISTAN | AFGHANISTAN Mobile | 5
> 3 | -1 | 0 | (^00355) | ALBANIA | ALBANIA | 6
> 4 | -1 | 0 | (^0035538) | ALBANIA | ALBANIA Mobile | 5
> 5 | -1 | 0 | (^0035568) | ALBANIA | ALBANIA Mobile | 5
> 6 | -1 | 0 | (^0035569) | ALBANIA | ALBANIA Mobile | 5
> 7 | -1 | 0 | (^00213) | ALGERIA | ALGERIA | 6
> 8 | -1 | 0 | (^0021361) | ALGERIA | ALGERIA Mobile | 5
> 9 | -1 | 0 | (^0021362) | ALGERIA | ALGERIA Mobile | 5
> 10 | -1 | 0 | (^0021363) | ALGERIA | ALGERIA Mobile | 5
>
> Now , I have to match a dialednumber (let's say 00213618833) and find it's destination...(It's algeria mobile).
> I tried to make with a query of using LIKE , but i was not able to get something..
Another idea would be to add some extra rows so that you could use
normal inequality searches. For example, let's take the Albanian rows:
3 | -1 | 0 | 00355
4 | -1 | 0 | 0035538
* 3 | -1 | 0 | 0035539
5 | -1 | 0 | 0035568
6 | -1 | 0 | 0035569
* 3 | -1 | 0 | 0035570
Now you can do "SELECT * FROM destlist WHERE ? >= prefix ORDER BY prefix
LIMIT 1".
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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-performance@postgresql.org
Cc: heikki@enterprisedb.com, alexandru.coseru@totaltelecom.ro, dave@tridecap.com
Subject: Re: Regex performance issue
In-Reply-To: <4571F856.7090808@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox