Re: Rules, Windows and ORDER BY - Mailing list pgsql-general

Jason Dusek <jason.dusek@gmail.com> 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.

            regards, tom lane