Re^4: Key to use for files in db!

I see following solutions in PostgreSQL:

1/ supposing btree unique index on path column

SELECT * FROM mytable 
WHERE path = '/my/path/to/file/isthis.dat';
[download]

I guess that it could be OK when database finely tuned. But optimizer could decide that index is not selective.

2/ supposing hash unique index on path column - the same as previous but index will be selective, i hope. This index type is not very recommended.

3/ supposing (functional) unique btree index on md5(path):

SELECT * FROM mytable 
WHERE md5(path) = md5('/my/path/to/file/isthis.dat');
[download]

Comment on Re^4: Key to use for files in db! Select or Download Code

Replies are listed 'Best First'.
Re^5: Key to use for files in db! by Ace128 (Hermit) on Jan 17, 2006 at 19:17 UTC
Hmm, kewl. Now, the question is, what is the BEST (fastest) way? #1 or #3? In this case #1 seems better, since it doesnt have to calculate the md5 twice. And maybe its better to have a column with that already calculated, and not calculate twice.	[reply]
Re^6: Key to use for files in db! by pajout (Curate) on Jan 18, 2006 at 18:23 UTC
Why do you think that the fastest means the best? :>))) But I am afraid of your problem is not fully resolvable without some experiments. Ad #3 - the md5 hash is (re)calculated when the _index_ built or modified, so the special column is not necessary. And, when the query runs, only md5 of queried string is calculated.	[reply]