Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id C980765030E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 13:51:02 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 52736-02-4
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 13 Nov 2008 13:50:55 -0400 (AST)
X-Greylist: delayed 01:37:30.57497 by SQLgrey-1.7.6
Received: from rn-out-0910.google.com (rn-out-0910.google.com
 [64.233.170.184])
	by mail.postgresql.org (Postfix) with ESMTP id 89DA06503E7
	for <pgsql-admin@postgresql.org>; Thu, 13 Nov 2008 13:21:50 -0400 (AST)
Received: by rn-out-0910.google.com with SMTP id k40so910419rnd.0
	for <pgsql-admin@postgresql.org>; Thu, 13 Nov 2008 09:21:49 -0800 (PST)
Received: by 10.142.230.7 with SMTP id c7mr3789406wfh.97.1226591078608;
	Thu, 13 Nov 2008 07:44:38 -0800 (PST)
Received: by 10.143.157.6 with HTTP; Thu, 13 Nov 2008 07:44:38 -0800 (PST)
Message-ID: <9b1af80e0811130744o7421d79bue68d58b04167a929@mail.gmail.com>
Date: Thu, 13 Nov 2008 18:44:38 +0300
From: "Vyacheslav Kalinin" <vka@mgcp.com>
To: "Julius Tuskenis" <julius@nsoft.lt>
Subject: Re: function executes sql 100 times longer it should
Cc: pgsql-admin@postgresql.org
In-Reply-To: <491C2BA5.7080401@nsoft.lt>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_33595_1405761.1226591078607"
References: <491AE3F2.601@nsoft.lt>
	<1226500247.2221.2.camel@lin-it1.eaglepress.local>
	<491BE279.50702@nsoft.lt>
	<9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com>
	<491C2BA5.7080401@nsoft.lt>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200811/71
X-Sequence-Number: 30725

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

Apparently your problem starts here:

> ->  Function Scan on filter_b_preke_matoma  (cost=0.00..267.50 rows=5
width=126) (actual time=6.580..11.766 rows=2820 loops=1)
>                                                  Filter:
(((prek_pavadinimas)::text ~~* (('%'::text || ($3)::text) || '%'::text)) OR
($3 IS NULL))

Planner expects to see only somewhat 5 rows after function scan with the
filter but get ~3000, which is not a surprise if one looks at your plain SQL
query, corresponding WHERE part:

 AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)

As I mentioned conditions like this get wrapped (to TRUE in your case), so
with plain SQL planner does not even try to estimate ILIKE filter effect.

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

Apparently your problem starts here:<br><br>&gt; -&gt;&nbsp; Function Scan on filter_b_preke_matoma&nbsp; (cost=0.00..267.50 rows=5 width=126) (actual time=6.580..11.766 rows=2820 loops=1)<br>&gt;&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; Filter: (((prek_pavadinimas)::text ~~* ((&#39;%&#39;::text || ($3)::text) || &#39;%&#39;::text)) OR ($3 IS NULL))<br>
<br>Planner expects to see only somewhat 5 rows after function scan with the filter but get ~3000, which is not a surprise if one looks at your plain SQL query, corresponding WHERE part:<br><br>&nbsp;AND ((prek_pavadinimas ILIKE (&#39;%&#39;||null||&#39;%&#39;)) OR null is NULL)<br>
<br>As I mentioned conditions like this get wrapped (to TRUE in your case), so with plain SQL planner does not even try to estimate ILIKE filter effect.<br><br><br>

------=_Part_33595_1405761.1226591078607--