0

I want to improve search performance for a key (Postgresql 9.3).

in a table like this:

CREATE TABLE kvstore (
  key varchar(128) PRIMARY KEY,
  value text,
  entry_date date,
  expire_date date,
);

I have no idea how posgresql stores strings as keys, if i convert keys to hashes can i improve the search performance or is it already optimized by postgresql, Do you have suggestions for this kind of data structures? like using hstore or something else?

yet
  • 773
  • 11
  • 19
  • 1
    If you store the hash as `bytea` the storage size will be less than half of the hexadecimal text so the index size will be smaller too leading to a significant performance improvement. [Check my answer](http://stackoverflow.com/a/15982876/131874) – Clodoaldo Neto Apr 23 '14 at 16:02
  • Hi @ClodoaldoNeto i read your answer, i think it's good for exact matches if it's supported with btree index. I will add an extra field named keyhash. But what about full text searches with 'like'? – yet Apr 23 '14 at 17:11
  • I tried this but it does not work as expected: SELECT * FROM kvstore WHERE to_tsvector('english', key) @@ to_tsquery('english', 'x'); it doesn't match keys like xx,xxx... – yet Apr 23 '14 at 17:29
  • What exactly is your "key" - what's the data? Also, show `EXPLAIN ANALYZE` output for the query you feel is slow. It looks to me a bit like you might be re-inventing [EAV](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model); if so, seriously consider whether you should be using json/xml/hstore instead, and whether you're using the right database platform for your task. – Craig Ringer Apr 24 '14 at 01:25

0 Answers0