· 7 years ago · Mar 01, 2019, 02:08 AM
1CREATE TABLE IF NOT EXISTS ip_range_domains (
2 ip_range_domain_id BIGSERIAL PRIMARY KEY,
3 domain_id BIGINT REFERENCES domains NOT NULL,
4 source_type_id INTEGER REFERENCES source_types NOT NULL,
5 low INET NOT NULL,
6 high INET NOT NULL,
7 auto_high_conf BOOLEAN NOT NULL DEFAULT FALSE,
8 invalidation_reason_id INTEGER REFERENCES invalidation_reasons DEFAULT NULL,
9 invalidated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
10 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT current_timestamp
11);
12CREATE INDEX domain_id_btree ON ip_range_domains (domain_id);
13CREATE INDEX idx_ip_range_inversed ON ip_range_domains(low, high);
14
15=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE '8.8.8.8'::INET BETWEEN low AND high;
16 QUERY PLAN
17------------------------------------------------------------------------------------------------------------------------------------------
18 Bitmap Heap Scan on ip_range_domains (cost=25411.02..278369.96 rows=948529 width=55) (actual time=61.514..61.567 rows=55 loops=1)
19 Recheck Cond: (('8.8.8.8'::inet >= low) AND ('8.8.8.8'::inet <= high))
20 Heap Blocks: exact=23
21 Buffers: shared hit=3613
22 -> Bitmap Index Scan on idx_ip_range_inversed (cost=0.00..25173.89 rows=948529 width=0) (actual time=61.493..61.493 rows=55 loops=1)
23 Index Cond: (('8.8.8.8'::inet >= low) AND ('8.8.8.8'::inet <= high))
24 Buffers: shared hit=3590
25 Planning time: 0.537 ms
26 Execution time: 61.631 ms
27
28=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE '200.8.8.8'::INET BETWEEN low AND high;
29 QUERY PLAN
30------------------------------------------------------------------------------------------------------------------------------
31 Seq Scan on ip_range_domains (cost=0.00..586084.02 rows=1016801 width=55) (actual time=14090.840..21951.343 rows=1 loops=1)
32 Filter: (('200.8.8.8'::inet >= low) AND ('200.8.8.8'::inet <= high))
33 Rows Removed by Filter: 23156868
34 Buffers: shared hit=21232 read=217499
35 Planning time: 0.111 ms
36 Execution time: 21951.376 ms
37
38=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE '74.181.234.146'::INET BETWEEN low AND high;
39 QUERY PLAN
40---------------------------------------------------------------------------------------------------------------------------------------------
41 Bitmap Heap Scan on ip_range_domains (cost=256258.42..580278.67 rows=5685950 width=55) (actual time=593.066..593.068 rows=3 loops=1)
42 Recheck Cond: (('74.181.234.146'::inet >= low) AND ('74.181.234.146'::inet <= high))
43 Heap Blocks: exact=3
44 Buffers: shared hit=38630
45 -> Bitmap Index Scan on idx_ip_range_inversed (cost=0.00..254836.93 rows=5685950 width=0) (actual time=593.057..593.057 rows=3 loops=1)
46 Index Cond: (('74.181.234.146'::inet >= low) AND ('74.181.234.146'::inet <= high))
47 Buffers: shared hit=38627
48 Planning time: 0.108 ms
49 Execution time: 593.094 ms
50
51=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE '74.181.234.147'::INET BETWEEN low AND high;
52 QUERY PLAN
53-----------------------------------------------------------------------------------------------------------------------------
54 Seq Scan on ip_range_domains (cost=0.00..586084.02 rows=5685950 width=55) (actual time=5723.461..21914.826 rows=3 loops=1)
55 Filter: (('74.181.234.147'::inet >= low) AND ('74.181.234.147'::inet <= high))
56 Rows Removed by Filter: 23156866
57 Buffers: shared hit=21864 read=216867
58 Planning time: 0.108 ms
59 Execution time: 21914.850 ms
60
61=> SELECT version();
62
63 version
64---------------------------------------------------------------------------------------------------------------
65 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit