Using postgresql UNIQUE INDEX and FUNCTIONAL INDEX together

Question

I have a postgresql table like below.

CREATE TABLE "user" (
"id" integer NOT NULL,
"hash" char(40) NOT NULL,
"username" char(255) NOT NULL,
PRIMARY KEY ("id"),
UNIQUE ("hash"));

However, since hash is 40 letter, I want to do a functional index like below to reduce the memory requirement.

CREATE INDEX CONCURRENTLY on user (substr(hash, 0, 20))

Is it okay to do like that or will it just generate another useless index? How can I make sure that UNIQUE index only index first 20 char from my hash?

Thanks.

If hash is HASH-function result, have you considered to store it as binary? It will reduce storage costs twice. Then, you can add it to UNIQUE index. — BlitZ
– BlitZ, Commented Aug 16, 2016 at 3:29
@Num6, yup. but will it introduce the cost to convert to and fro from binary to string? — moeseth
– moeseth, Commented Aug 16, 2016 at 4:58

Craig Ringer · Accepted Answer · 2016-08-16 05:44:14Z

3

If you need the hash to be unique, you must have a unique index on the whole thing. Otherwise you'll get unique violations for hashes that differ only in the last 20 chars.

You can create a non-unique index on the left 20 chars, like you showed:

CREATE INDEX on user (left(hash, 20))

But it probably serves no useful purpose. PostgreSQL will create a unique index on the whole 40 char hash automatically when you declare it as a a UNIQUE constraint. You cannot drop this index without dropping the constraint. So you're stuck with the full size index if you want to enforce uniqueness of hashes. Given that, it's unlikely that the functional index will be of much benefit. Even in queries like:

SELECT ...
FROM "user"
WHERE left(hash, 20) = left($1, 20) AND hash = $1

where you might think you're saving time by using a smaller index to do a quick check first, in reality it's fairly likely that PostgreSQL will ignore the functional index and prefer the full index since it's more selective.

I'm not totally clear on what you're trying to achieve, but if it's doing a partial or functional index to implement a unique constraint, you can't do that.

Also, store hash as bytea and use the index expression left(hash, 20). Or maybe 10, if you're currently storing as a 2-chars-per-byte hex representation.

answered Aug 16, 2016 at 5:44

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

moeseth Over a year ago

Thanks bro. This is exactly the answer I'm looking for.

e4c5 Over a year ago

if the answer is correct, it's customary to mark it as correct.

Collectives™ on Stack Overflow

Using postgresql UNIQUE INDEX and FUNCTIONAL INDEX together

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related