Re: function executes sql 100 times longer it should - Mailing list pgsql-admin

From Vyacheslav Kalinin
Subject Re: function executes sql 100 times longer it should
Date
Msg-id 9b1af80e0811130744o7421d79bue68d58b04167a929@mail.gmail.com
Whole thread Raw
In response to Re: function executes sql 100 times longer it should  (Julius Tuskenis <julius@nsoft.lt>)
Responses Re: function executes sql 100 times longer it should
List 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.


pgsql-admin by date:

Previous
From: Julius Tuskenis
Date:
Subject: Re: function executes sql 100 times longer it should
Next
From: Isabella Ghiurea
Date:
Subject: db restore question/issue