Re: Creation of tsearch2 index is very slow

Tom Lane wrote:
> Stephan Vollmer <svollmer@gmx.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