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… |
- [PERFORM] tsearch2 seem very slow Ahmad Fajar
- Re: [PERFORM] tsearch2 seem very slow Oleg Bartunov
- Re: [PERFORM] tsearch2 seem very slow Oleg Bartunov
- Re: [PERFORM] tsearch2 seem very slow Ahmad Fajar
- Re: [PERFORM] tsearch2 seem very slow Oleg Bartunov
- Re: [PERFORM] tsearch2 seem very slow Ahmad Fajar
- Re: [PERFORM] tsearch2 seem very slow Oleg Bartunov
- Re: [PERFORM] tsearch2 seem very s... Ahmad Fajar
- Re: [PERFORM] tsearch2 seem ve... Oleg Bartunov
- Re: [PERFORM] tsearch2 seem ve... Ahmad Fajar