Thread: inet/cidr indexes almost not used
Your name : Gleb Kouzmenko
Your email address : gleb@well.ru
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)
OK, see the FAQ on index usage and run some tests.
I have just added the following to our FAQ section on index usage:
<P>If you believe the optimizer is incorrect in choosing a
sequential scan, use <CODE>SET enable_seqscan TO 'off'</CODE> and
run tests to see if an index scan is indeed faster.</P>
---------------------------------------------------------------------------
Gleb Kouzmenko wrote:
> Your name : Gleb Kouzmenko
> Your email address : gleb@well.ru
>
>
> 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
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce,
I did SET enable_seqscan to off before EXPLAINs, of course.
Optimizer ignored this SET, and I cannot compare seq scan with index one.
I quoted two EXPLAINs below again: WHERE i<<'IP' and WHERE 'IP'>>i is
logically equivalent, but are planned differently
(BTW I thought that inet/cidr ops >>,>>=,<<,<<= cannot be used with indexes at all
until I had read thread 'inet regression test' in c.d.p.hackers a couple days ago)
Thank you for your support.
Bruce Momjian wrote:
> OK, see the FAQ on index usage and run some tests.
>
> I have just added the following to our FAQ section on index usage:
>
> <P>If you believe the optimizer is incorrect in choosing a
> sequential scan, use <CODE>SET enable_seqscan TO 'off'</CODE> and
> run tests to see if an index scan is indeed faster.</P>
>
> Gleb Kouzmenko wrote:
[...]
>>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)
[...]
Gleb Kouzmenko <gleb@well.ru> writes:
> I quoted two EXPLAINs below again: WHERE i<<'IP' and WHERE 'IP'>>i is
> logically equivalent, but are planned differently
If you'd like to fix that, see match_special_index_operator() and
expand_indexqual_conditions() in src/backend/optimizer/path/indxpath.c.
I can't get very excited about it myself.
regards, tom lane