From: | "Ahmad Fajar" <fajar(at)it-indonesia(dot)info> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | tsearch2 seem very slow |
Date: | 2005-09-21 22:08:12 |
Message-ID: | SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gurpreet Aulakh | 2005-09-22 00:11:17 | Re: Query slower on 8.0.3 (Windows) vs 7.3 (cygwin) |
Previous Message | Gurpreet Aulakh | 2005-09-21 19:38:20 | Query slower on 8.0.3 (Windows) vs 7.3 (cygwin) |