From: Dmitriy Kurilov <dmkurilov(at)mail(dot)ru>
To: pgsql-novice(at)postgresql(dot)org
Subject:
Date: 2005-11-09 05:25:06
Message-ID: E1EZiSI-000EOD-00.dmkurilov-mail-ru@f40.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2005-11-09 13:15:30 Dump/restore needed from 8.1b1 to 8.1.0?
Previous Message John DeSoi 2005-11-08 18:56:26 Re: simple or global column names?