Tom Lane wrote: > Stephan Vollmer <svollmer@xxxxxx> 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
Attachment:
signature.asc
Description: OpenPGP digital signature
