Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id E1A9E9F9F7B
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  2 Dec 2006 18:13:40 -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 58757-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat,  2 Dec 2006 18:13:37 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.totaltelecom.ro (mail.totaltelecom.ro [89.36.0.12])
	by postgresql.org (Postfix) with ESMTP id 46A099F9EED
	for <pgsql-performance@postgresql.org>;
	Sat,  2 Dec 2006 18:13:36 -0400 (AST)
Received: from mail.totaltelecom.ro (localhost [127.0.0.1])
	by mail.totaltelecom.ro (Postfix) with ESMTP id 0B10734BE24;
	Sun,  3 Dec 2006 00:13:35 +0200 (EET)
Received: by mail.totaltelecom.ro (Postfix, from userid 99)
	id DDC0534BE9F; Sun,  3 Dec 2006 00:13:34 +0200 (EET)
Received: from alex (unknown [86.107.100.199])
	by mail.totaltelecom.ro (Postfix) with ESMTP id 80CA734BE24;
	Sun,  3 Dec 2006 00:13:33 +0200 (EET)
Message-ID: <022c01c7165f$17f49e80$3cb16956@alex>
From: "Alexandru Coseru" <alexandru.coseru@totaltelecom.ro>
To: "Heikki Linnakangas" <heikki@enterprisedb.com>
Cc: <pgsql-performance@postgresql.org>
References: <030b01c71651$984577c0$8300a8c0@tridecap.com>
	<01ea01c71653$438254e0$3cb16956@alex>
	<4571F856.7090808@enterprisedb.com>
Subject: Re: Regex performance issue
Date: Sun, 3 Dec 2006 00:13:27 +0200
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-Virus-Scanned: ClamAV using ClamSMTP
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/18
X-Sequence-Number: 22074

Hello..

I cannot use the first advice , because i'm not aware of the prefix length 
in the database...
This is why i'm ordering after length(prefix)..


On the 2nd one , i'm not sure that i can follow you..

Regards
    Alex
----- Original Message ----- 
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
To: "Alexandru Coseru" <alexandru.coseru@totaltelecom.ro>
Cc: "Dave Dutcher" <dave@tridecap.com>; <pgsql-performance@postgresql.org>
Sent: Sunday, December 03, 2006 12:04 AM
Subject: Re: [PERFORM] Regex performance issue


> 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
>
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.15.4/563 - Release Date: 12/2/2006
>
>