Re: gist index woes - Mailing list pgsql-general

From Pierre-Frédéric Caillaud
Subject Re: gist index woes
Date
Msg-id opseqm43mzcq72hf@musicbox
Whole thread Raw
In response to Re: gist index woes  (Oleg Bartunov <oleg@sai.msu.su>)
List 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 by date:

Previous
From: Konstantin Danilov
Date:
Subject: start psql
Next
From: "Jim C. Nasby"
Date:
Subject: Re: using database for queuing operations?