How do you do, everybody!
I have a problem with indexes on column of type cidr and inet. In particular, I have table containing column of type
cidr.
CREATE TABLE test (key serial PRIMARY KEY, net cidr NOT NULL);
I've created index on "net" column.
CREATE INDEX test_net ON test(net);
But query
EXPLAIN ANALYZE SELECT * FROM test WHERE '10.0.0.1/32'::cidr <<= net;
returns
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..1432.22 rows=32769 width=36) (actual time=0.021..59.058 rows=2 loops=1)
Filter: ('10.0.0.1/32'::cidr <<= net)
Total runtime: 59.122 ms
(записей: 3)
while query
EXPLAIN ANALYZE SELECT * FROM test WHERE '10.0.0.1/32'::cidr = net;
returns
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using test_net on test (cost=0.00..3.01 rows=1 width=36) (actual time=0.033..0.033 rows=0 loops=1)
Index Cond: ('10.0.0.1/32'::cidr = net)
Total runtime: 0.102 ms
(записей: 3)
How to force PostgreSQL to use index on cidr and inet types?
I'd used searching but unsuccessfully.
My PostgreSQL version:
PostgreSQL 8.0.2 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728
--
b.r.,
Kurilov Dmitriy.
