Message-ID: <43D114B1.5030802@gmx.de>
Date: Fri, 20 Jan 2006 17:49:53 +0100
From: Stephan Vollmer <svollmer@gmx.de>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8) Gecko/20051201 Thunderbird/1.5 Mnenhy/0.7.3.0
MIME-Version: 1.0
To: pgsql-general@postgresql.org
Subject: Re: Creation of tsearch2 index is very slow
References: <43D0ED57.1020608@gmx.de> <17454.1137771321@sss.pgh.pa.us>
In-Reply-To: <17454.1137771321@sss.pgh.pa.us>
OpenPGP: id=8D46FCF8
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig6AA55D676317398731675352"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig6AA55D676317398731675352
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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?
>=20
> 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 optima=
l
> 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-hangin=
g
> fruit there.  It'd be interesting to look at a gprof profile of what th=
e
> 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


--------------enig6AA55D676317398731675352
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFD0RS0jY05go1G/PgRAiu5AJ9qZJD6NQR6A4SVAsYQEF8pEJgGWwCcC05D
68LXcNq5ydTl2V+biygQlKY=
=xArD
-----END PGP SIGNATURE-----

--------------enig6AA55D676317398731675352--