From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Alexandru Coseru <alexandru.coseru@totaltelecom.ro>
Cc: pgsql-performance@postgresql.org
Subject: Re: Regex performance issue
Date: Sat, 02 Dec 2006 22:35:18 +0000
Message-ID: <4571FFA6.6070804@enterprisedb.com> (raw)
In-Reply-To: <022c01c7165f$17f49e80$3cb16956@alex>
References: <030b01c71651$984577c0$8300a8c0@tridecap.com>
<01ea01c71653$438254e0$3cb16956@alex>
<4571F856.7090808@enterprisedb.com>
<022c01c7165f$17f49e80$3cb16956@alex>
Alexandru Coseru wrote:
> 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..
Ok, let me try again :)
> 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
Store the prefix in a character column, without the regex stuff. Like
below. I've removed the columns that are not relevant, in fact it would
make sense to store them in another table, and have just the id and
prefix in this table.
id | prefix | network
---+---------+--------------------
1 | 0093 | AFGHANISTAN
2 | 00937 | AFGHANISTAN Mobile
3 | 00355 | ALBANIA
4 | 0035538 | ALBANIA Mobile
5 | 0035568 | ALBANIA Mobile
6 | 0035569 | ALBANIA Mobile
7 | 00213 | ALGERIA
8 | 0021361 | ALGERIA Mobile
9 | 0021362 | ALGERIA Mobile
10 | 0021363 | ALGERIA Mobile
Now, add the rows marked with start below:
id | prefix | network
----+---------+--------------------
1 | 0093 | AFGHANISTAN
2 | 00937 | AFGHANISTAN Mobile
* 1 | 00938 | AFGHANISTAN
3 | 00355 | ALBANIA
4 | 0035538 | ALBANIA Mobile
* 3 | 0035539 | ALBANIA
5 | 0035568 | ALBANIA Mobile
6 | 0035569 | ALBANIA Mobile
* 3 | 003557 | ALBANIA
7 | 00213 | ALGERIA
8 | 0021361 | ALGERIA Mobile
9 | 0021362 | ALGERIA Mobile
10 | 0021363 | ALGERIA Mobile
* 7 | 0021364 | ALGERIA
The added rows make it unnecessary to use regex for the searches. You
can use just the >= operator like this: (using the example number you gave)
SELECT id FROM destlist WHERE '00213618833' >= prefix ORDER BY prefix
LIMIT 1
Which would return id 7. As another example, a query of "00213654321"
would match the last row, which again has id 7.
I'm too tired to figure out the exact algorithm for adding the rows, but
I'm pretty sure it can be automated... The basic idea is that when
there's a row with id A and prefix XXXX, and another row with id B and
prefix XXXXY, we add another row with id A and prefix XXXX(Y+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
Subject: Re: Regex performance issue
In-Reply-To: <4571FFA6.6070804@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