pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Index scan on CIDR field ?
4+ messages / 3 participants
[nested] [flat]

* Index scan on CIDR field ?
@ 1999-12-29 08:46 Margarit Nickolov <man@digsys.bg>
  1999-12-29 14:52 ` Re: [NOVICE] Index scan on CIDR field ? D. Duccini <duccini@backpack.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Margarit Nickolov @ 1999-12-29 08:46 UTC (permalink / raw)
  To: pgsql-novice@postgresql.org; pgsql-sql

  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. I need a index scan. Do I miss something ?
Margarit.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [NOVICE] Index scan on CIDR field ?
  1999-12-29 08:46 Index scan on CIDR field ? Margarit Nickolov <man@digsys.bg>
@ 1999-12-29 14:52 ` D. Duccini <duccini@backpack.com>
  0 siblings, 0 replies; 4+ messages in thread

From: D. Duccini @ 1999-12-29 14:52 UTC (permalink / raw)
  To: Margarit Nickolov <man@digsys.bg>; +Cc: pgsql-novice@postgreSQL.org; pgsql-sql


did you create an index?

create index_name on tablename (row_name);

you would also get one automatically if when you created the table you'd
made that row a PRIMARY KEY

-duck

On Wed, 29 Dec 1999, Margarit Nickolov wrote:

>   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. I need a index scan. Do I miss something ?
> Margarit.
> 
> 
> ************
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Index scan on CIDR field ?
@ 1999-12-29 08:52 Margarit Nickolov <man@digsys.bg>
  1999-12-29 15:47 ` Re: [SQL] Index scan on CIDR field ? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Margarit Nickolov @ 1999-12-29 08:52 UTC (permalink / raw)
  To: pgsql-sql

  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. I need a index scan. Do I miss something ?
Margarit.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Index scan on CIDR field ?
  1999-12-29 08:52 Index scan on CIDR field ? Margarit Nickolov <man@digsys.bg>
@ 1999-12-29 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 1999-12-29 15:47 UTC (permalink / raw)
  To: Margarit Nickolov <man@digsys.bg>; +Cc: pgsql-sql; pgsql-hackers@postgreSQL.org

Margarit Nickolov <man@digsys.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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-12-29 15:47 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-29 08:46 Index scan on CIDR field ? Margarit Nickolov <man@digsys.bg>
1999-12-29 14:52 ` D. Duccini <duccini@backpack.com>
1999-12-29 08:52 Index scan on CIDR field ? Margarit Nickolov <man@digsys.bg>
1999-12-29 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox