1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
set client_min_messages = warning;
create table prefixes (
prefix text primary key,
name text not null,
shortname text,
state char default 'S',
check( state in ('S', 'R') )
);
comment on column prefixes.state is 'S: assigned - R: reserved';
\copy prefixes from 'prefixes.fr.csv' with delimiter ';' csv quote '"'
create table ranges as select prefix::prefix_range, name, shortname, state from prefixes ;
create index idx_prefix on ranges using gist(prefix gist_prefix_range_ops);
analyze ranges;
set enable_seqscan to off;
select * from ranges where prefix @> '0146640123';
select * from ranges where prefix @> '0100091234';
set enable_seqscan to on;
select * from ranges where prefix @> '0146640123';
select * from ranges where prefix @> '0100091234';
select a, b, pr_penalty(a::prefix_range, b::prefix_range)
from (values('095[4-5]', '0[8-9]'),
('095[4-5]', '0[0-9]'),
('095[4-5]', '[0-3]'),
('095[4-5]', '0'),
('095[4-5]', '[0-9]'),
('095[4-5]', '0[1-5]'),
('095[4-5]', '32'),
('095[4-5]', '[1-3]')) as t(a, b)
order by 3 asc;
create table numbers(number text primary key);
insert into numbers
select '01' || substr(regexp_replace(md5(i::text), '[a-f]', '', 'g'), 1, 8)
from generate_series(1, 5000) i;
analyze numbers;
select count(*) from numbers n join ranges r on r.prefix @> n.number;
reset client_min_messages;
-- Debian Bug 690160 regarding the symetry of <@ and @>
SELECT count(*) FROM ranges WHERE prefix <@ '01000';
SELECT count(*) FROM ranges WHERE prefix @> '01000';
SELECT count(*) FROM ranges WHERE '01000' <@ prefix;
SELECT count(*) FROM ranges WHERE '01000' @> prefix;
SELECT count(*) FROM ranges WHERE '010009888' @> prefix;
SELECT count(*) FROM ranges WHERE '010009888' <@ prefix;
SELECT count(*) FROM ranges WHERE prefix @> '010009888';
SELECT count(*) FROM ranges WHERE prefix <@ '010009888';
|
