Re: [SQL] Index scan on CIDR field ?

Margarit Nickolov <man(at)digsys(dot)bg> writes:
> I created index on cidr field on table with about 1 000 000 rows, made
> 'vacuum analyze', but exlain told me that sequental scan is done
> on query like that SELECT * FROM table WHERE ipaddr='212.129.92.1'
> I'm using PostgreSQL 6.5.1.

Hmm. I think this is an artifact of the recently noticed mistake in
6.5's pg_opclass table: it uses the same name "network_ops" for two
different index operator classes.

I found that current sources seem to work properly:

create table cidr1 (f1 cidr);
create index cidri on cidr1 (f1);
explain select * from cidr1 where f1 = '212.129.92.1';

Index Scan using cidri on cidr1 (cost=2.50 rows=10 width=12)

but 6.5.3 chooses a sequential scan, just as Margarit says.

Furthermore the pg_index entry for cidri is wrong in 6.5.3;
it shows indclass = 935 (pg_opclass entry for inet) whereas current
sources show 652 (the one for cidr). I haven't bothered to track down
exactly where the confusion occurs in the code, but I'll bet some part
of index creation is assuming that index opclass names are unique.
The wrong pg_index entry explains why the optimizer is ignoring the
index; it's looking for one whose opclass matches the cidr '=' op
it's trying to optimize.

Margarit, I think you can fix this in a 6.5.* database as follows:
as postgres, say

UPDATE pg_opclass SET opcname = 'inet_ops' WHERE oid = 935;
UPDATE pg_opclass SET opcname = 'cidr_ops' WHERE oid = 652;

Then drop and recreate the faulty index(es). (Probably any index you
have on a cidr column is messed up.)

Better back up your database before trying this!!! It seemed to work
in a play database, but I make no guarantees.

Note to hackers: perhaps we should recommend that anyone using inet or
cidr indexes do this? If they don't, when it comes time to update to
7.0 their pg_dumped index declarations will fail, since 7.0 won't
recognize "network_ops" as an index opclass name.

regards, tom lane