2012/8/23 Tom Lane <tgl@xxxxxxxxxxxxx>: > Jason Dusek <jason.dusek@xxxxxxxxx> writes: >> I have a simple table of keys and values which periodically >> receives updated values. It's desirable to keep older values >> but, most of the time, we query only for the latest value of a >> particular key. > >> CREATE TABLE kv >> ( k bytea NOT NULL, >> at timestamptz NOT NULL, >> realm bytea NOT NULL, >> v bytea NOT NULL ); >> CREATE INDEX ON kv USING hash(k); >> CREATE INDEX ON kv (t); >> CREATE INDEX ON kv USING hash(realm); > >> SELECT * FROM kv WHERE k = $1 AND realm = $2 ORDER BY at DESC LIMIT 1; > > If you want to make that fast, an index on (k,realm,at) would > help. Those indexes that you did create are next to useless > for this, and furthermore hash indexes are quite unsafe for > production. Thanks for pointing out the unsafety of hash indexes. I think I got in the habit of using them for a project with large, temporary data sets. Why are the individual indices not useful? The tests that the query does -- equality on key and realm and ordering on at -- are each supported by indices. Does it have to do with the cost of loading the three indices? -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
