DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=G4NN2mErAi1BqYdwcSMEUhkoPxiypP86kXfNu0Pf96AJNb+YcjpnYtxiOFMU8zBsIn4mRQId+/1f5V5QyoMF3CFl9kwTfM5qNsS8+2aTmLw0rfUrnn4DAFfpQaMj5kSrpfixa69zfeb1pTxoUNS73qnEZa5L1p+SWhVTGKSufaA=
Message-ID: <caec019b0802050347t4cd46203w7d087d3d51ac2b11@mail.gmail.com>
Date: Tue, 5 Feb 2008 12:47:47 +0100
From: "Viviane Lestic" <vlestic@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Performance issue using Tsearch2
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I'm having a performance problem on a request using Tsearch2: the
request strangely takes several minutes.

I've tried to follow Tsearch tuning recommendations, I've searched
through the archives, but I can't seem to find a solution to solve my
problem.

The ts_vector field was created using dictionnary fr_ispell only on
types lword, lpart_hword and lhword. An index was created on this
field.

According to the stat() function, there are only 42,590 word stems indexed.
I also did a VACUUM FULL ANALYZE.

Here's the result of EXPLAIN ANALYZE on a filtered version of my
request (the non-filtered version takes so long I usually cancel it):
**************************************************************************
explain analyze SELECT idstruct, headline(zonetext, q),
rank(zoneindex_test, q) FROM tab_ocr, tab_chemin, to_tsquery('partir')
AS q WHERE tab_chemin.chemin like '%;2;%' AND tab_ocr.idstruct =
tab_chemin.label AND zoneindex_test @@ q ORDER BY rank(zoneindex_test,
q) DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2345.54..2345.58 rows=16 width=308) (actual
time=270638.774..270643.142 rows=7106 loops=1)
Sort Key: rank(tab_ocr.zoneindex_test, q.q)
-> Nested Loop (cost=80.04..2345.22 rows=16 width=308) (actual
time=40886.553..270619.730 rows=7106 loops=1)
-> Nested Loop (cost=80.04..1465.76 rows=392 width=308) (actual
time=38209.193..173932.313 rows=272414 loops=1)
-> Function Scan on q (cost=0.00..0.01 rows=1 width=32) (actual
time=0.006..0.007 rows=1 loops=1)
-> Bitmap Heap Scan on tab_ocr (cost=80.04..1460.85 rows=392
width=276) (actual time=38209.180..173507.052 rows=272414 loops=1)
Filter: (tab_ocr.zoneindex_test @@ q.q)
-> Bitmap Index Scan on zoneindex_test_idx (cost=0.00..79.94 rows=392
width=0) (actual time=38204.261..38204.261 rows=283606 loops=1)
Index Cond: (tab_ocr.zoneindex_test @@ q.q)
-> Index Scan using tab_chemin_label_index on tab_chemin
(cost=0.00..2.23 rows=1 width=4) (actual time=0.036..0.036 rows=0
loops=272414)
Index Cond: (tab_ocr.idstruct = tab_chemin.label)
Filter: ((chemin)::text ~~ '%;2;%'::text)
Total runtime: 270647.946 ms
**************************************************************************

Could someone help me analyze this problem?
I don't manage to see if the problem comes from bad tsearch tuning,
postgresql configuration, or something else...

Thanks.