bug in hash indexes???

From: Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz>
To: <pgsql-general(at)postgresql(dot)org>
Subject: bug in hash indexes???
Date: 2001-07-20 12:35:05
Message-ID: Pine.LNX.4.33.0107201345260.8557-100000@u-pl0
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi,
i observed strange behavior using hash index. is it a known bug?

the index was created before loading data into table. when i recreate the
tc01_tc01pk____idx index everything is ok.

and i can see some other strange things with hash indexes in other tables.
is it possible that i'm using hash indexes in a bad way? or is it because
of using multiple indexes on one column?

any ideas?

thanks,
kuba

db_c01=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4

-- running debian, kernel 2.4, installed from .deb package

db_c01=# \d tc01
Table "tc01"
Attribute | Type | Modifier
-----------+-----------------------+--------------------------------------------------------
tc01pk___ | integer | not null default
nextval('"tc01_tc01pk____seq"'::text)
tc01mobil | character varying(30) | not null
tc01jmeno | character varying(50) |
tc01prijm | character varying(50) |
tc01zusta | double precision | not null
tc03pk___ | integer | not null
tc01akt__ | boolean | not null default 't'::bool
Indices: tc01_pkey,
tc01_tc01pk____idx,
tc01_tc01prijm_idx

-- tc01_tc01pk is a hash index on tc01pk___
-- tc01_pkey is a btree index on tc01pk___

db_c01=# explain select oid, * from tc01 order by tc01pk___ desc LIMIT 1;
Limit (cost=0.00..0.06 rows=1 width=57)
-> Index Scan Backward using tc01_pkey on tc01 (cost=0.00..59.00
rows=1000 width=57)

db_c01=# select oid, * from tc01 order by tc01pk___ desc LIMIT 1;
oid | tc01pk___ | tc01mobil | tc01jmeno | tc01prijm | tc01zusta |
tc03pk__
---------+-----------+------------+-----------+-----------+-----------+---------
1469963 | 10003 | 0609123456 | pepa | vomacka | 10000 |
(1 row)

db_c01=# explain select oid, * from tc01 where tc01pk___ = 10003;
Index Scan using tc01_tc01pk____idx on tc01 (cost=0.00..8.14 rows=10
width=57)

db_c01=# select oid, * from tc01 where tc01pk___ = 10003;
oid | tc01pk___ | tc01mobil | tc01jmeno | tc01prijm | tc01zusta |
tc03pk___ | t
-----+-----------+-----------+-----------+-----------+-----------+-----------+--
(0 rows)

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond Chui 2001-07-20 13:36:11 When PostgreSQL compliant JDBC 2.0?
Previous Message The Hermit Hacker 2001-07-20 12:11:54 Re: Good software takes 10 years