From: Vyacheslav Kalinin <vka@mgcp.com>
To: Julius Tuskenis <julius@nsoft.lt>
Cc: pgsql-admin@postgresql.org
Subject: Re: function executes sql 100 times longer it should
Date: Thu, 13 Nov 2008 18:44:38 +0300
Message-ID: <9b1af80e0811130744o7421d79bue68d58b04167a929@mail.gmail.com> (raw)
In-Reply-To: <491C2BA5.7080401@nsoft.lt>
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>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: vka@mgcp.com, julius@nsoft.lt
Subject: Re: function executes sql 100 times longer it should
In-Reply-To: <9b1af80e0811130744o7421d79bue68d58b04167a929@mail.gmail.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