Re: Creation of tsearch2 index is very slow

Tom Lane wrote:
> Stephan Vollmer <svollmer(at)gmx(dot)de> writes:
>> I noticed that the creation of a GIST index for tsearch2 takes very
>> long - about 20 minutes. CPU utilization is 100 %, the resulting
>> index file size is ~25 MB. Is this behaviour normal?
>
> This has been complained of before. GIST is always going to be slower
> at index-building than btree; in the btree case there's a simple optimal
> strategy for making an index (ie, sort the keys) but for GIST we don't
> know anything better to do than insert the keys one at a time.

Ah, ok. That explains a lot, although I wonder why it is so much slower.

> However, I'm not sure that anyone's tried to do any performance
> optimization on the GIST insert code ... there might be some low-hanging
> fruit there. It'd be interesting to look at a gprof profile of what the
> backend is doing during the index build. Do you have the ability to do
> that, or would you be willing to give your data to someone else to
> investigate with?

Unfortunately, I'm not able to investigate it further myself as I'm
quite a Postgres newbie. But I could provide someone else with the
example table. Maybe someone else could find out why it is so slow.

I dropped all unnecessary columns and trimmed the table down to
235,000 rows. The dumped table (compressed with RAR) is 7,1 MB. I
don't have a website to upload it but I could send it to someone via
e-mail.

With this 235,000 row table, index creation times are:
- GIST 347063 ms
- B-Tree 2515 ms

Thanks for your help!

- Stephan