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:[EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
