Обсуждение: CIDR index use for '<<' operator

Postgres doesn't seem to make use of indexes when doing operations with the
CIDR '<<' (contains) operator.  Is this intended behavior or impossible for
some reason?


test=> \d a
          Table "a"
 Attribute | Type | Modifier
-----------+------+----------
 a         | cidr | not null
Index: a_pkey

test=> EXPLAIN SELECT * FROM a WHERE a.a = '192.168.0.128/32'::cidr;
NOTICE:  QUERY PLAN:

Index Scan using a_pkey on a  (cost=0.00..2.16 rows=1 width=12)

EXPLAIN
test=> EXPLAIN SELECT * FROM a WHERE a.a << '192.168.0.128/30'::cidr;
NOTICE:  QUERY PLAN:

Seq Scan on a  (cost=0.00..1205.20 rows=32768 width=12)

EXPLAIN
test=>


--
Kendall Koning
Senior Network Engineer, egl.net
Ph: (616) 392-9949 x26

"Kendall Koning" <kkoning@egl.net> writes:
> Postgres doesn't seem to make use of indexes when doing operations with the
> CIDR '<<' (contains) operator.

You're right --- the system has no idea that the '<<' operator has any
relationship to the sort ordering of CIDR indexes.

Seems like it'd be possible to improve this along the same lines that
we use to make LIKE and regexp matches indexable: derive lower and
upper bounds on the CIDR variable from a 'cidr-var << cidr-constant'
clause, and use those to create 'cidr-var >= lower-bound AND
cidr-var <= upper-bound' indexscan limit clauses.

If you feel like tackling this, the "special index operator" support
in src/backend/optimizer/path/indxpath.c is the stuff that'd need to
be extended.

            regards, tom lane

Added to TODO.

> "Kendall Koning" <kkoning@egl.net> writes:
> > Postgres doesn't seem to make use of indexes when doing operations with the
> > CIDR '<<' (contains) operator.
>
> You're right --- the system has no idea that the '<<' operator has any
> relationship to the sort ordering of CIDR indexes.
>
> Seems like it'd be possible to improve this along the same lines that
> we use to make LIKE and regexp matches indexable: derive lower and
> upper bounds on the CIDR variable from a 'cidr-var << cidr-constant'
> clause, and use those to create 'cidr-var >= lower-bound AND
> cidr-var <= upper-bound' indexscan limit clauses.
>
> If you feel like tackling this, the "special index operator" support
> in src/backend/optimizer/path/indxpath.c is the stuff that'd need to
> be extended.
>
>             regards, tom lane
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026