pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ahmad Fajar <fajar@it-indonesia.info>
To: pgsql-performance@postgresql.org
Subject: tsearch2 seem very slow
Date: Thu, 22 Sep 2005 05:08:12 +0700
Message-ID: <SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com> (raw)

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.

view thread (10+ messages)  latest in thread

Message-ID: <SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com>
Permalink:  ../SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com/
Also on:    postgresql.org/message-id/SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: fajar@it-indonesia.info
  Subject: Re: tsearch2 seem very slow
  In-Reply-To: <SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com>
  References: <SVONEVY5jVkjA2DJxRV0000018d@ki-communication.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox