· 7 years ago · Dec 12, 2018, 07:06 PM
1-- Performance test
2-- Comparing ip address as bigint and as ip4 (ip4r extension)
3
4DROP TABLE IF EXISTS test_perf_ip_bigint;
5CREATE TABLE IF NOT EXISTS test_perf_ip_bigint (
6 ip bigint
7);
8
9INSERT INTO test_perf_ip_bigint
10SELECT ip
11FROM dns_a_record
12LIMIT 100000000; -- 4 m 22 s 629 ms
13
14-- ==
15
16DROP TABLE IF EXISTS test_perf_ip_ip4;
17CREATE TABLE IF NOT EXISTS test_perf_ip_ip4(
18 id serial primary key,
19 ip ip4
20); -- 54 ms
21
22INSERT INTO test_perf_ip_ip4 (ip)
23SELECT '0.0.0.0'::inet + ip::bigint
24FROM test_perf_ip_bigint;
25
26CREATE INDEX ON test_perf_ip_ip4 (ip); -- 1 m 24 s 681 ms
27
28-- ==
29
30EXPLAIN ANALYZE select ip from test_perf_ip_bigint order by ip limit 1 offset 10000000; -- 843041288
31-- Execution time: 57884.519 ms
32
33EXPLAIN ANALYZE select ip from test_perf_ip_ip4 order by ip limit 1 offset 10000000; -- 50.63.202.8
34-- Execution time: 63290.957 ms
35
36
37EXPLAIN ANALYZE select ip from test_perf_ip_bigint order by ip limit 1 offset 30000000; -- 917265804
38-- Execution time: 159797.954 ms
39
40EXPLAIN ANALYZE select ip from test_perf_ip_ip4 order by ip limit 1 offset 30000000; -- 54.172.93.140
41-- Execution time: 170381.270 ms
42
43
44EXPLAIN ANALYZE select ip from test_perf_ip_bigint order by ip limit 1 offset 50000000; -- 1740880243
45-- Execution time: 41646.495 ms
46
47EXPLAIN ANALYZE select ip from test_perf_ip_ip4 order by ip limit 1 offset 50000000; -- 103.195.185.115
48-- Execution time: 42063.196 ms
49
50
51EXPLAIN ANALYZE select ip from test_perf_ip_bigint order by ip limit 1 offset 90000000; -- 3423429981
52-- Execution time: 56151.450 ms
53
54EXPLAIN ANALYZE select ip from test_perf_ip_ip4 order by ip limit 1 offset 90000000; -- 204.13.101.93
55-- Execution time: 57322.361 ms
56
57
58EXPLAIN ANALYZE select ip from test_perf_ip_bigint where ip = 843041288; -- Execution time: 673.709 ms
59EXPLAIN ANALYZE select ip from test_perf_ip_ip4 where ip = '50.63.202.8'; -- Execution time: 715.694 ms
60
61EXPLAIN ANALYZE select ip from test_perf_ip_bigint where ip = 917265804; -- Execution time: 5.632 ms
62EXPLAIN ANALYZE select ip from test_perf_ip_ip4 where ip = '54.172.93.140'; -- Execution time: 4.532 ms
63
64EXPLAIN ANALYZE select ip from test_perf_ip_bigint where ip = 1740880243; -- Execution time: 24.293 ms
65EXPLAIN ANALYZE select ip from test_perf_ip_ip4 where ip = '103.195.185.115'; -- Execution time: 113.270 ms
66
67EXPLAIN ANALYZE select ip from test_perf_ip_bigint where ip = 3423429981; -- Execution time: 1.433 ms
68EXPLAIN ANALYZE select ip from test_perf_ip_ip4 where ip = '204.13.101.93'; -- Execution time: 0.385 ms
69
70
71EXPLAIN ANALYZE
72select ip -- Execution time: 0.057 ms
73from test_perf_ip_bigint
74where ip
75 between 336860160 -- 20.20.20.0
76 and 336860415; -- 20.20.20.255
77
78EXPLAIN ANALYZE
79SELECT ip -- Execution time: 0.054 ms
80FROM test_perf_ip_ip4
81WHERE ip
82 BETWEEN lower('20.20.20.0/24'::ip4r)
83 AND upper('20.20.20.0/24'::ip4r);
84
85
86EXPLAIN ANALYZE
87select distinct ip -- Execution time: 0.865 ms
88from test_perf_ip_bigint
89where ip
90 between 168430080 -- 10.10.10.0
91 and 168430335; -- 10.10.10.255
92
93EXPLAIN ANALYZE
94select distinct ip -- Execution time: 0.864 ms
95FROM test_perf_ip_ip4
96WHERE ip
97 BETWEEN lower('10.10.10.0/24'::ip4r)
98 AND upper('10.10.10.0/24'::ip4r);
99
100
101EXPLAIN ANALYZE
102select distinct ip -- Execution time: 2.032 ms
103from test_perf_ip_bigint
104where ip
105 between 168427520 -- 10.10.0.0
106 and 168493055; -- 10.10.255.255
107
108EXPLAIN ANALYZE
109SELECT distinct ip -- Execution time: 2.123 ms
110FROM test_perf_ip_ip4
111WHERE ip
112 BETWEEN lower('10.10.0.0/16'::ip4r)
113 AND upper('10.10.0.0/16'::ip4r);
114
115
116EXPLAIN ANALYZE
117select distinct ip -- Execution time: 15.920 ms
118from test_perf_ip_bigint
119where ip
120 between 4194304000 -- 250.0.0.0
121 and 4278190080; -- 255.0.0.0
122
123EXPLAIN ANALYZE
124SELECT distinct ip -- Execution time: 26.360 ms
125FROM test_perf_ip_ip4
126WHERE ip
127 BETWEEN lower('250.0.0.0/8'::ip4r)
128 AND upper('255.0.0.0/8'::ip4r);