On Fri, Aug 24, 2012 at 09:32:32AM +0000, Jason Dusek wrote:
> 2012/8/23 Tom Lane <tgl@sss.pgh.pa.us>:
> > Jason Dusek <jason.dusek@gmail.com> writes:
> >> 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.
>
> 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?
I'm not entirely sure, but I'll take a stab at it. I think it has to do
with the fact that you want order. Combining multiple indexes so you
use them at the same time works as an BitmapAnd. That is, it uses each
index to determine blocks that are interesting and then find the blocks
that are listed by all tindexes, and then it loads the blocks and chcks
them.
The problem here is that you want ORDER BY at, which makes the above
scheme fall apart, because order is not preversed. So it falls back on
either scanning the 'at' index and probing checking the rows to see if
they match, or using all indexes, and then sorting the result.
In theory you could BitmapAnd the 'k' and 'realm' indexes and then scan
the 'at' index only checking rows that the bitmap shows are
interesting. But I'm not sure if postgres can do that.
Anyway, the suggested three column index will match your query in a
single lookup and hence be much faster than any of the above
suggestions, so if this is a really important query then it may be
worth it here.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer
