Re: gist index woes
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: gist index woes |
Дата | |
Msg-id | opseqm43mzcq72hf@musicbox обсуждение исходный текст |
Ответ на | Re: gist index woes (Oleg Bartunov <oleg@sai.msu.su>) |
Список | pgsql-general |
Thanks for the hint, it did work. The table weights 270 MB, the index weights 580 MB, but searches are fast. > Have you tried gist__intbig_ops ? > > Oleg > On Tue, 21 Sep 2004, [iso-8859-15] Pierre-Fr?d?ric Caillaud wrote: > >> >> Trying to build a gist index on a column in a table. >> The table contains 100k rows. >> The column is an integer[]. Each row contains about 20-30 distinct >> values >> chosen between 1 and 437. >> Aim : search the arrays with the gist integer array operators @ etc. >> >> Creating the index with gist__int_ops takes forever and, after >> something >> like one hour, fills the disk to the brim. There is about 4G free space >> on >> the partition and it eats it completely. >> >> Doing the same with only 10k rows takes forever too. Bumping up >> sort_mem >> to 128 Meg does nothing. Only way to make it work is to create the index >> on an empty table, and insert the rows afterwards. I only tried 10K rows >> as disk space consumption is alarming. >> >> Search speed in this 10k rows using the index is a lot slower (10x) >> than >> sequential scan. >> >> A problem with this contrib module ? >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> > > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
В списке pgsql-general по дате отправления: