From: | "Neil Conway" <nconway(at)klamath(dot)dyndns(dot)org> |
---|---|
To: | allane(at)cybaea(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Known problem with HASH index? |
Date: | 2001-07-10 13:07:04 |
Message-ID: | 9611.159.33.2.50.994770424.squirrel@klamath.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> Is there a known problem with HASH type index in PostgreSQL 7.1.2
> 4PGDG on Red Hat Linux 7.1 (2.4.2 kernel)? I can't find a lot of
> documentation, but this is what I observe:
Tom Lane has mentioned several times that index types other than BTREE
have suffered some bit-rot over the past few years, and probably have
problems with concurrent access -- so using a BTREE is recommended
whenever possible. It would be cool if someone felt like taking the
time to clean up HASH indexes though...
> [playpen]$ dropdb test; createdb test; psql -f create_table.sql
> test; psql -c "COPY clients FROM '/tmp/input.txt';" test; psql -c
> 'CREATE INDEX clients_idx ON clients USING HASH (tel);' test;
> vacuumdb test; vacuumdb --analyze test DROP DATABASE
Doing vacuumdb followed by vacuumdb --analyze is redundant; doing a
VACUUM ANALYZE also does a regular VACUUM.
> NOTICE: Index clients_idx: NUMBER OF INDEX' TUPLES (916864) IS NOT
> THE SAME AS HEAP' (1000000).
> Recreate the index.
That's strange... Personally, I'd just switch to a BTREE, where
presumambly this won't occur. However, I'd suggest waiting for one of
the Postgres hackers to give you a proper answer ;-)
> [playpen]$ cat create_table.sql
> CREATE TABLE clients (
> ClientID integer,
> firstname varchar(5),
> surname varchar(22),
> area varchar(3),
> neigh varchar(27),
> dimos varchar(50),
> tel varchar(7)
The missing ');' at the end is a typo, right?
> The input file is a bit big to include, but was created using this
> brain-damaged perl script (somebody please teach me how to do
> random letter strings :-))
Grab stuff from /usr/share/dict?
>srand(time||$$);
On modern Perls, the default srand() seed is more secure than this, I
believe. (Although it doesn't matter for your script, of course)
Cheers,
Neil
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Jais | 2001-07-10 13:15:53 | editor with syntax-highlighting for PL/PGSQL |
Previous Message | Martijn van Oosterhout | 2001-07-10 12:57:52 | Re: [PATCH] Partial indicies again |