On Fri, Aug 24, 2012 at 09:32:32AM +0000, Jason Dusek wrote: > 2012/8/23 Tom Lane <tgl@xxxxxxxxxxxxx>: > > Jason Dusek <jason.dusek@xxxxxxxxx> 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@xxxxxxxxx> 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
Attachment:
signature.asc
Description: Digital signature
