Your name : Gleb Kouzmenko
Your email address : [EMAIL PROTECTED]
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.19
PostgreSQL version (example: PostgreSQL-7.3.1): PostgreSQL-7.3.1 (REL7_3_STABLE 2003-01-16)
Compiler used (example: gcc 2.95.2) : gcc 3.2
Please enter a FULL description of your problem:
------------------------------------------------
I almost never could not use single-column index on cidr or inet fields
for ops <<,<<=,>>,>>= - optimizer does seq scan instead of index scan.
index scan happens only when
( ops is << or ops is <<= ) and column is left-side operand
Examples
____________
[table and rows from src/test/regress/sql/inet.sql]
CREATE TABLE INET_TBL (c cidr, i inet);
INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24');
... inserts from inet.sql
create index inet_idx1 on inet_tbl(i);
create index inet_idx2 on inet_tbl(c);
=========
test=# set enable_seqscan to off;
SET
test=# explain select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using inet_idx1 on inet_tbl (cost=0.00..4.68 rows=7 width=64)
Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <= '192.168.1.255'::inet))
Filter: (i << '192.168.1.0/24'::inet)
(2 rows)
test=# explain select * from inet_tbl where '192.168.1.0/24'::cidr>>i;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on inet_tbl (cost=100000000.00..100000001.17 rows=7 width=64)
Filter: ('192.168.1.0/24'::inet >> i)
(2 rows)
test=# explain select * from inet_tbl where c>>'192.168.1.0/24'::cidr;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on inet_tbl (cost=100000000.00..100000001.17 rows=7 width=64)
Filter: (c >> '192.168.1.0/24'::cidr)
(2 rows)
test=# explain select * from inet_tbl where '192.168.1.0/24'::cidr<<c;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on inet_tbl (cost=100000000.00..100000001.17 rows=7 width=64)
Filter: ('192.168.1.0/24'::cidr << c)
(2 rows)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
