Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 8729F2E2F6C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 08:12:25 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 40325-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri,  8 Feb 2008 08:12:09 -0400 (AST)
Received: from rv-out-0910.google.com (rv-out-0910.google.com
 [209.85.198.190])
	by postgresql.org (Postfix) with ESMTP id 514A82E023D
	for <pgsql-performance@postgresql.org>;
	Fri,  8 Feb 2008 08:12:11 -0400 (AST)
Received: by rv-out-0910.google.com with SMTP id g13so2794834rvb.43
	for <pgsql-performance@postgresql.org>;
	Fri, 08 Feb 2008 04:12:09 -0800 (PST)
Received: by 10.140.251.1 with SMTP id y1mr8443227rvh.102.1202472729119;
	Fri, 08 Feb 2008 04:12:09 -0800 (PST)
Received: by 10.140.133.19 with HTTP; Fri, 8 Feb 2008 04:12:09 -0800 (PST)
Message-ID: <2d0127b80802080412o741fa5a1p3a3ae241e21574f7@mail.gmail.com>
Date: Fri, 8 Feb 2008 14:12:09 +0200
From: "Yonatan Ben-Nes" <yonatan@epoch.co.il>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Wrong number of rows estimation by the planner
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_30975_5442795.1202472729119"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200802/91
X-Sequence-Number: 28633

------=_Part_30975_5442795.1202472729119
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi all,

When I'm doing an explain analyze to a query of mine I notice that the
number of estimated rows by the planner is a lot smaller then the actual
number of rows, I'm afraid that this make my queries slower.

A query for example is:

EXPLAIN ANALYZE
SELECT product_id,product_name
FROM product
WHERE product_keywords_vector @@ plainto_tsquery('default', 'black') AND
rank(product_keywords_vector, plainto_tsquery('default', 'black')) > 0.4 AND
product_status = TRUE AND product_type = 'comparison'
ORDER BY ((product_buy_number * 4) + product_view_number + 1) *
rank(product_keywords_vector, plainto_tsquery('default', 'black')) DESC;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10543.67..10544.81 rows=455 width=297) (actual time=
1098.188..1104.606 rows=22248 loops=1)
   Sort Key: (((((product_buy_number * 4) + product_view_number) +
1))::double precision * rank(product_keywords_vector, '''black'''::tsquery))
   ->  Bitmap Heap Scan on product  (cost=287.13..10523.59 rows=455
width=297) (actual time=50.496..1071.900 rows=22248 loops=1)
         Recheck Cond: (product_keywords_vector @@ '''black'''::tsquery)
         Filter: ((rank(product_keywords_vector, '''black'''::tsquery) >
0.4::double precision) AND product_status AND (product_type =
'comparison'::text))
         ->  Bitmap Index Scan on product_product_keywords_vector  (cost=
0.00..287.02 rows=2688 width=0) (actual time=26.385..26.385 rows=72507
loops=1)
               Index Cond: (product_keywords_vector @@ '''black'''::tsquery)
 Total runtime: 1111.507 ms
(8 rows)

Here as I understand it, at the Bitmap Index Scan on
product_product_keywords_vector the planner estimate that it will retrieve
2688 rows but it actually retrieve 72507 rows and later at the Bitmap Heap
Scan on product it estimate 455 rows and retrieve 22248 rows.

I increased the statistics of the field which the
product_product_keywords_vector index is built on by doing:
ALTER TABLE product ALTER COLUMN product_keywords_vector SET STATISTICS
1000;
ANALYZE;
REINDEX INDEX product_product_keywords_vector;

But it didn't change a thing.

Any ideas?

Thanks in advance,
Yonatan Ben-Nes

------=_Part_30975_5442795.1202472729119
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi all,<br><br>When I&#39;m doing an explain analyze to a query of mine I notice that the number of estimated rows by the planner is a lot smaller then the actual number of rows, I&#39;m afraid that this make my queries slower.<br>
<br>A query for example is:<br><br>EXPLAIN ANALYZE<br>SELECT product_id,product_name<br>FROM product<br>WHERE product_keywords_vector @@ plainto_tsquery(&#39;default&#39;, &#39;black&#39;) AND rank(product_keywords_vector, plainto_tsquery(&#39;default&#39;, &#39;black&#39;)) &gt; 0.4 AND<br>
product_status = TRUE AND product_type = &#39;comparison&#39;<br>ORDER BY ((product_buy_number * 4) + product_view_number + 1) * rank(product_keywords_vector, plainto_tsquery(&#39;default&#39;, &#39;black&#39;)) DESC;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN<br>------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Sort&nbsp; (cost=10543.67..10544.81 rows=455 width=297) (actual time=1098.188..1104.606 rows=22248 loops=1)<br>&nbsp;&nbsp; Sort Key: (((((product_buy_number * 4) + product_view_number) + 1))::double precision * rank(product_keywords_vector, &#39;&#39;&#39;black&#39;&#39;&#39;::tsquery))<br>
&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on product&nbsp; (cost=287.13..10523.59 rows=455 width=297) (actual time=50.496..1071.900 rows=22248 loops=1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: (product_keywords_vector @@ &#39;&#39;&#39;black&#39;&#39;&#39;::tsquery)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((rank(product_keywords_vector, &#39;&#39;&#39;black&#39;&#39;&#39;::tsquery) &gt; 0.4::double precision) AND product_status AND (product_type = &#39;comparison&#39;::text))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on product_product_keywords_vector&nbsp; (cost=0.00..287.02 rows=2688 width=0) (actual time=26.385..26.385 rows=72507 loops=1)<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (product_keywords_vector @@ &#39;&#39;&#39;black&#39;&#39;&#39;::tsquery)<br>&nbsp;Total runtime: 1111.507 ms<br>(8 rows)<br><br>Here as I understand it, at the Bitmap Index Scan on product_product_keywords_vector the planner estimate that it will retrieve 2688 rows but it actually retrieve 72507 rows and later at the Bitmap Heap Scan on product it estimate 455 rows and retrieve 22248 rows.<br>
<br>I increased the statistics of the field which the product_product_keywords_vector index is built on by doing:<br>ALTER TABLE product ALTER COLUMN product_keywords_vector SET STATISTICS 1000;<br>ANALYZE;<br>REINDEX INDEX product_product_keywords_vector;<br>
<br>But it didn&#39;t change a thing.<br><br>Any ideas?<br><br>Thanks in advance,<br>Yonatan Ben-Nes<br><br><br><br><br>

------=_Part_30975_5442795.1202472729119--