DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=QCqq/C8HEfb/2koSI1EOyrGvQg7KMK5jkyKHGixTijsgnQpdKRYAgXPI7zxLvumDY2jx3UyTbXmH1vUOxk9lHseFdzSnBGYwSeqwTKNWX9rCSSnezaPWxUqRXEsleX6fUyERJBzHKydqIyQQX2fKZ2HlMr2GpV34lYvRQjkAVWY=
Message-ID: <caec019b0802050708x4d40fe40he7a6682fe371c9df@mail.gmail.com>
Date: Tue, 5 Feb 2008 16:08:36 +0100
From: "Viviane Lestic" <vlestic@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Performance issue using Tsearch2
In-Reply-To: <1d4e0c10802050548u2ebbc553na46ea977a78febd1@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <caec019b0802050347t4cd46203w7d087d3d51ac2b11@mail.gmail.com>
	<1d4e0c10802050548u2ebbc553na46ea977a78febd1@mail.gmail.com>

2008/2/5, Ansgar -59cobalt- Wiechers wrote:
> Your planner estimates are way off. Try increasing the statistics target
> for the columns used in this query and re-analyze the tables after doing
> so.

I first set STATISTICS to 1000 for column zoneindex_test and saw no
significant improvement (with a vacuum full analyze in between). Then
I set default_statistics_target to 1000: there is now an improvement,
but the overall time is still way too long... (and the estimated costs
didn't change...)
Here are the results with default_statistics_target set to 1000:

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.30..2345.32 rows=8 width=327) (actual
time=229913.715..229918.172 rows=7106 loops=1)
   Sort Key: rank(tab_ocr.zoneindex_test, q.q)
   ->  Nested Loop  (cost=80.04..2345.18 rows=8 width=327) (actual
time=28159.626..229892.957 rows=7106 loops=1)
         ->  Nested Loop  (cost=80.04..1465.76 rows=392 width=327)
(actual time=26084.558..130979.395 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=295) (actual time=26084.544..130562.220 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=26073.315..26073.315
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.040..0.040 rows=0
loops=272414)
               Index Cond: (tab_ocr.idstruct = tab_chemin.label)
               Filter: ((chemin)::text ~~ '%;2;%'::text)
 Total runtime: 229922.864 ms


2008/2/5, Guillaume Smet wrote:
> Can you try to replace zoneindex_test @@ q with zoneindex_test @@
> to_tsquery('partir')?

The improvement seems negligible (with default_statistics_target back
to 10, its default value):
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 @@ to_tsquery('partir') ORDER BY
rank(zoneindex_test, q) DESC;

  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4358.91..4358.95 rows=16 width=308) (actual
time=266489.667..266494.132 rows=7106 loops=1)
   Sort Key: rank(tab_ocr.zoneindex_test, q.q)
   ->  Nested Loop  (cost=80.04..4358.59 rows=16 width=308) (actual
time=42245.881..266469.644 rows=7106 loops=1)
         ->  Function Scan on q  (cost=0.00..0.01 rows=1 width=32)
(actual time=0.007..0.008 rows=1 loops=1)
         ->  Nested Loop  (cost=80.04..4358.34 rows=16 width=276)
(actual time=42239.570..178496.761 rows=7106 loops=1)
               ->  Bitmap Heap Scan on tab_ocr  (cost=80.04..1461.83
rows=392 width=276) (actual time=38317.423..174188.779 rows=272414
loops=1)
                     Filter: (zoneindex_test @@ '''partir'''::tsquery)
                     ->  Bitmap Index Scan on zoneindex_test_idx
(cost=0.00..79.94 rows=392 width=0) (actual time=38289.289..38289.289
rows=283606 loops=1)
                           Index Cond: (zoneindex_test @@ '''partir'''::tsquery)
               ->  Index Scan using tab_chemin_label_index on
tab_chemin  (cost=0.00..7.38 rows=1 width=4) (actual time=0.014..0.014
rows=0 loops=272414)
                     Index Cond: (tab_ocr.idstruct = tab_chemin.label)
                     Filter: ((chemin)::text ~~ '%;2;%'::text)
 Total runtime: 266498.704 ms

> Increasing the statistics for zoneindex_test may
> be a good idea too (see ALTER TABLE ... ALTER COLUMN doc).

I posted the results above.

> I'm surprised you have the word "partir" in so many documents? Do you
> use real data?

I'm using real data. The indexed documents are extracted from
newspapers, and "partir" (and its derivates) is quite a common verb in
the French language, so I'm not that surprised to see it show up in
many documents.