Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
Le mercredi 21 février 2007 15:44, Tom Lane a écrit : > Markus Schiltknecht <[EMAIL PROTECTED]> writes: > > Henrik Zagerholm wrote: > >> Is this normal? What can I tweak in postgresql.conf to speed up big > >> to_tsvector()? > > > > Hm.. seems not too unreasonable to me. > > Take a look at the stemmers or dictionaries involved. What do you use > > there? > > Also, I wonder how much of the time went into to_tsvector() and how much > into updating the GIST or GIN index that I suppose is on the column. > For a full update like this it might make sense to drop the index, > update the column, rebuild the index from scratch. a count of distinct word in those 194 000 can be interesting also, I presume. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
21 feb 2007 kl. 15:44 skrev Tom Lane: Markus Schiltknecht <[EMAIL PROTECTED]> writes: Henrik Zagerholm wrote: Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? Hm.. seems not too unreasonable to me. Take a look at the stemmers or dictionaries involved. What do you use there? I tried both the default english and simple stemmers. Simple was a little bit faster but not much. What is your configuration ? to_tsvector does a lot of work. I haven't tweaked the postgresql.conf yet. What should I focus on? work_mem, shared_buffers? Also, I wonder how much of the time went into to_tsvector() and how much into updating the GIST or GIN index that I suppose is on the column. For a full update like this it might make sense to drop the index, update the column, rebuild the index from scratch. I actually tested to remove the GIN index on the vectors column and it didn't have any noticeable effect. Maybe because its the only tuple in the database. Thanks for all your input! regards, henrik regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
Markus Schiltknecht <[EMAIL PROTECTED]> writes: > Henrik Zagerholm wrote: >> Is this normal? What can I tweak in postgresql.conf to speed up big >> to_tsvector()? > Hm.. seems not too unreasonable to me. > Take a look at the stemmers or dictionaries involved. What do you use there? Also, I wonder how much of the time went into to_tsvector() and how much into updating the GIST or GIN index that I suppose is on the column. For a full update like this it might make sense to drop the index, update the column, rebuild the index from scratch. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
On Wed, 21 Feb 2007, Henrik Zagerholm wrote: Hello list, I've been testing tsearch2 for a while and I recently noticed some really slow queries. This is a quite big document so the times are maybe accurate. The document has about 194 000 words. I put all the data in tbl_fulltext.fulltext_text and then did a update tbl_fulltext set vectors = to_tsvector(fulltext_text); Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? What is your configuration ? to_tsvector does a lot of work. Regards, Henrik ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
Hi, Henrik Zagerholm wrote: Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? Hm.. seems not too unreasonable to me. Take a look at the stemmers or dictionaries involved. What do you use there? Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
Henrik Zagerholm wrote: The document has about 194 000 words. update tbl_fulltext set vectors = to_tsvector(fulltext_text); Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? Is this a problem? How many 194,000 word documents do you index? If you have many, you might want to separate them into chapters. I think tsearch2 is more targetted at web-pages or short PDFs rather than whole books. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
Hello list, I've been testing tsearch2 for a while and I recently noticed some really slow queries. This is a quite big document so the times are maybe accurate. The document has about 194 000 words. I put all the data in tbl_fulltext.fulltext_text and then did a update tbl_fulltext set vectors = to_tsvector(fulltext_text); Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? Regards, Henrik ---(end of broadcast)--- TIP 6: explain analyze is your friend
