Re: tsearch2 seem very slow - Mailing list pgsql-performance

On Fri, 23 Sep 2005, Ahmad Fajar wrote:

> Hi Oleg,
>
> I didn't deny on the third repeat or more, it can reach < 600 msec. It is
> only because the result still in postgres cache, but how about in the first
> run? I didn't dare, the values is un-acceptable. Because my table will grows
> rapidly, it's about 100000 rows per-week. And the visitor will search
> anything that I don't know, whether it's the repeated search or new search,
> or whether it's in postgres cache or not.

if you have enoush shared memory postgresql will keep index pages there.


>
> I just compare with http://www.postgresql.org, the search is quite fast, and
> I don't know whether the site uses tsearch2 or something else. But as fas as
> I know, if the rows reach >100 milion (I have try for 200 milion rows and it
> seem very slow), even if don't use tsearch2, only use simple query like:
> select f1, f2 from table1 where f2='blabla',
> and f2 is indexes, my postgres still slow on the first time, about >10 sec.
> because of this I tried something brand new to fullfill my needs. I have
> used fti, and tsearch2 but still slow.
>
> I don't know what's going wrong with my postgres, what configuration must I
> do to perform the query get fast result. Or must I use enterprisedb 2005 or
> pervasive postgres (both uses postgres), I don't know very much about these
> two products.

you didn't show us your configuration (hardware,postgresql and tsearch2),
explain analyze of your queries, so we can't help you.
How big is your database, tsearch2 index size ?


>
> Regards,
> ahmad fajar
>
>
> -----Original Message-----
> From: Oleg Bartunov [mailto:oleg@sai.msu.su]
> Sent: Jumat, 23 September 2005 14:36
> To: Ahmad Fajar
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] tsearch2 seem very slow
>
> Ahmad,
>
> how fast is repeated runs ? First time system could be very slow.
> Also, have you checked my page
> http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
> and some info about tsearch2 internals
> http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
>
>     Oleg
> On Thu, 22 Sep 2005, Ahmad Fajar wrote:
>
>> I have about 419804 rows in my article table. I have installed tsearch2
> and
>> its gist index correctly.
>>
>> My table structure is:
>>
>> CREATE TABLE tbarticles
>>
>> (
>>
>>  articleid int4 NOT NULL,
>>
>>  title varchar(250),
>>
>>  mediaid int4,
>>
>>  datee date,
>>
>>  content text,
>>
>>  contentvar text,
>>
>>  mmcol float4 NOT NULL,
>>
>>  sirkulasi float4,
>>
>>  page varchar(10),
>>
>>  tglisidata date,
>>
>>  namapc varchar(12),
>>
>>  usere varchar(12),
>>
>>  file_pdf varchar(255),
>>
>>  file_pdf2 varchar(50),
>>
>>  kolom int4,
>>
>>  size_jpeg int4,
>>
>>  journalist varchar(120),
>>
>>  ratebw float4,
>>
>>  ratefc float4,
>>
>>  fti tsvector,
>>
>>  CONSTRAINT pk_tbarticles PRIMARY KEY (articleid)
>>
>> ) WITHOUT OIDS;
>>
>> Create index fti_idx1 on tbarticles using gist (fti);
>>
>> Create index fti_idx2 on tbarticles using gist (datee, fti);
>>
>>
>>
>> But when I search something like:
>>
>> Select articleid, title, datee from tbarticles where fti @@
>> to_tsquery('susilo&bambang&yudhoyono&jusuf&kalla');
>>
>> It takes about 30 sec. I run explain analyze and the index is used
>> correctly.
>>
>>
>>
>> Then I try multi column index to filter by date, and my query something
>> like:
>>
>> Select articleid, title, datee from tbarticles where fti @@
>> to_tsquery('susilo&bambang&yudhoyono&jusuf&kalla') and datee >=
> '2002-01-01'
>> and datee <= current_date
>>
>> An it still run about 25 sec. I do run explain analyze and my multicolumn
>> index is used correctly.
>>
>> This is not acceptable if want to publish my website if the search took
> very
>> longer.
>>
>>
>>
>> I have run vacuum full analyze before doing such query. What going wrong
>> with my query?? Is there any way to make this faster?
>>
>> I have try to tune my postgres configuration, but it seem helpless. My
> linux
>> box is Redhat 4 AS, and
>>
>> the hardware: 2 GB RAM DDR 400, 2x200 GB Serial ATA 7200RPM and configure
> as
>> RAID0 (just for postgres data), my sistem run at EIDE 80GB 7200 RPM.
>>
>>
>>
>> Please.help.help.
>>
>>
>
>     Regards,
>         Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83