Skip site navigation (1) Skip section navigation (2)

Re: function executes sql 100 times longer it should

From: "Vyacheslav Kalinin" <vka(at)mgcp(dot)com>
To: "Julius Tuskenis" <julius(at)nsoft(dot)lt>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: function executes sql 100 times longer it should
Date: 2008-11-13 15:44:38
Message-ID: 9b1af80e0811130744o7421d79bue68d58b04167a929@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

In response to

Responses

pgsql-admin by date

Next:From: Isabella GhiureaDate: 2008-11-13 21:10:15
Subject: db restore question/issue
Previous:From: Julius TuskenisDate: 2008-11-13 13:29:09
Subject: Re: function executes sql 100 times longer it should

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group