Re: 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

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

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

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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Markus Schiltknecht <markus(at)bluegap(dot)ch> 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

Le mercredi 21 février 2007 15:44, Tom Lane a écrit :
> Markus Schiltknecht <markus(at)bluegap(dot)ch> 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

21 feb 2007 kl. 15:44 skrev Tom Lane:

> Markus Schiltknecht <markus(at)bluegap(dot)ch> 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