Re: keeping an index in memory

Rajarshi Guha <rguha(at)indiana(dot)edu> writes:
> Now, it might just be the case that given the size of the index, I
> cannot make bounding box queries (which will use the CUBE index) go
> any faster. But I am surprised that that the other type of query
> (using cube_distance which by definition must use a seq scan) is only
> slightly longer. If nothing else, scanning through 14GB of data
> should be 3 times slower than scanning through 3GB of data.

A single index probe should not touch anything like all of the index ---
unless your data is such that the index is very non-optimally laid out.
GiST should work well if there are lots of subsets of the data that
have bounding boxes disjoint from other subsets'. If not, maybe you
need to reconsider your data representation.

Have you done any examination of how much of the index gets touched
during a typical query? I'd try turning on stats_block_level and see
how the delta in pg_statio_all_indexes.idx_blks_read compares to the
index size.

regards, tom lane