Re: inet/cidr indexes almost not used - Mailing list pgsql-bugs

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