Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id A361C9F9F7B
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  2 Dec 2006 18:04:23 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 56459-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  2 Dec 2006 18:04:19 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from mail01.enterprisedb.com (mail01.enterprisedb.com
 [63.246.7.168])
	by postgresql.org (Postfix) with ESMTP id A7EF69F9EED
	for <pgsql-performance@postgresql.org>;
	Sat,  2 Dec 2006 18:04:18 -0400 (AST)
thread-index: AccWXdHmhagclgaYRrOQFx4d+7m4bQ==
Received: from [192.168.1.34] ([82.27.225.36]) by mail01.enterprisedb.com over
	TLS secured channel with Microsoft SMTPSVC(6.0.3790.1830);
	Sat, 2 Dec 2006 17:04:20 -0500
Message-ID: <4571F856.7090808@enterprisedb.com>
Content-Class: urn:content-classes:message
Importance: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757
Date: Sat, 02 Dec 2006 22:04:06 +0000
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060927)
MIME-Version: 1.0
To: "Alexandru Coseru" <alexandru.coseru@totaltelecom.ro>
Cc: "Dave Dutcher" <dave@tridecap.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Regex performance issue
References: <030b01c71651$984577c0$8300a8c0@tridecap.com>
	<01ea01c71653$438254e0$3cb16956@alex>
In-Reply-To: <01ea01c71653$438254e0$3cb16956@alex>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 02 Dec 2006 22:04:21.0203 (UTC)
	FILETIME=[D1DCDA30:01C7165D]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/17
X-Sequence-Number: 22073

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