Re: Gist indexes on int arrays

Greg Stark <gsstark(at)MIT(dot)EDU> writes:

> Can I have a GiST index on (foo_id, attribute_set_array) and have it be just
> as fast at narrowing the search to just foo_id = 900 but also speed up the ~
> operation?

Hm, so if I understand what I'm reading I can do this if I load the btree_gist
contrib module as well. I'm still not sure whether it'll be worthwhile for
this application though.

I have a bit of a problem though. Is building GiST indexes supposed to take
much much longer than building btree indexes? It's been running nearly an hour
and it's still going. The hard drive is hardly moving so it seems to be all
cpu usage. I don't even see any pgsql_tmp usage.

db=# CREATE INDEX cache_gist_idx on cache using gist ( foo_id , attribute_set gist__int_ops);

postgres 30176 86.3 22.2 64896 57344 ? R 11:08 40:32 postgres: postgres slo [local] CREATE INDEX

I don't remember exact numbers but building the normal btree index took on the
order of 15m. This will have to be rebuilt nightly, an hour long index build
won't be practical.

--
greg