pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex 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