Re: Predicates not getting pushed into SQL function? - Mailing list pgsql-performance

What other info can I provide?  id is int, gender is varchar(255), and it's happening on 9.0.4...<br /><blockquote
cite="mid:6646.1320345676@sss.pgh.pa.us"style="border: 0px none;" type="cite"><div class="__pbConvHr"
style="margin:30px25px 10px 25px;"><div style="display:table;width:100%;border-top:1px solid 
 
#EDEEF0;padding-top:5px"><div style="display:table-cell;vertical-align:middle;padding-right:6px;"><img height="25px"
name="compose-unknown-contact.jpg"photoaddress="tgl@sss.pgh.pa.us" photoname="Tom Lane"
src="cid:part1.06030504.03020905@gmail.com"width="25px" /></div><div
style="display:table-cell;white-space:nowrap;vertical-align:middle;width:100%"><fontcolor="#737F92"><span
style="padding-right:6px;font-weight:bold">TomLane</span></font></div><div
style="display:table-cell;white-space:nowrap;vertical-align:middle;"><fontcolor="#9FA2A5"><span
style="padding-left:6px">November3, 2011 2:41 PM</span></font></div></div></div><div  __pbrmquotes="true"
class="__pbConvBody"style="color:#888888;margin-left:24px;margin-right:24px;"><pre wrap="">Jay Levitt <a
class="moz-txt-link-rfc2396E"href="mailto:jay.levitt@gmail.com"><jay.levitt@gmail.com></a> writes:
 
</pre><blockquote type="cite"><pre wrap="">I'm confused.  I have a now-trivial SQL function that, unrestricted, would 
scan my whole users table.  When I paste the body of the function as a 
subquery and restrict it to one row, it only produces one row.  When I paste 
the body of the function into a view and restrict it to one row, it produces 
one row.  But when I put it in a SQL function... it scans the whole users 
table and then throws the other rows away.
</pre></blockquote><pre wrap="">
</pre><blockquote type="cite"><pre wrap="">I thought SQL functions were generally inline-able, push-down-able, etc.
</pre></blockquote><pre wrap="">
inline-able, yes, but if they're not inlined you don't get any such
thing as pushdown of external conditions into the function body.
A non-inlined function is a black box.

The interesting question here is why the function doesn't get inlined
into the calling query.  You got the obvious showstoppers: it has a
SETOF result, it's not volatile, nor strict.  The only other possibility
I can see offhand is that there's some sort of result datatype mismatch,
but you've not provided enough info to be sure about that.
        regards, tom lane
</pre></div><div class="__pbConvHr" style="margin:30px 25px 10px 25px;"><div
style="display:table;width:100%;border-top:1pxsolid 
 
#EDEEF0;padding-top:5px"><div style="display:table-cell;vertical-align:middle;padding-right:6px;"><img height="25px"
name="compose-unknown-contact.jpg"photoaddress="jay.levitt@gmail.com" photoname="Jay Levitt"
src="cid:part1.06030504.03020905@gmail.com"width="25px" /></div><div
style="display:table-cell;white-space:nowrap;vertical-align:middle;width:100%"><fontcolor="#737F92"><span
style="padding-right:6px;font-weight:bold">JayLevitt</span></font></div><div
style="display:table-cell;white-space:nowrap;vertical-align:middle;"><fontcolor="#9FA2A5"><span
style="padding-left:6px">November3, 2011 1:47 PM</span></font></div></div></div><div  __pbrmquotes="true"
class="__pbConvBody"style="color:#888888;margin-left:24px;margin-right:24px;">I'm confused.  I have a now-trivial SQL
functionthat, unrestricted, would scan my whole users table.  When I paste the body of the function as a subquery and
restrictit to one row, it only produces one row.  When I paste the body of the function into a view and restrict it to
onerow, it produces one row.  But when I put it in a SQL function... it scans the whole users table and then throws the
otherrows away. <br /><br />I thought SQL functions were generally inline-able, push-down-able, etc.  As a workaround,
Ican put my WHERE clause inside the function and pass it parameters, but that feels ugly, and it won't help for things
likeresticting via JOINs.  The real function needs parameters, so I can't use it as a view.  Are there better
workarounds?<br /><br />I suspect the problem is (something like) the planner doesn't realize the function will produce
avariable number of rows; I can specify COST or ROWS, but they're both fixed values. <br /><br />Pretty-printed
functionand explain analyze results: <br /><br /><a class="moz-txt-link-freetext"
href="https://gist.github.com/1336963">https://gist.github.com/1336963</a><br/><br />In ASCII for web-haters and
posterity:<br /><br />-- THE OVERLY SIMPLIFIED FUNCTION <br /><br />create or replace function matcher() <br />returns
table(user_idint, match int) as $$ <br /><br />    select o.user_id, 1 as match <br />    from ( <br />      select
u.idas user_id, u.gender <br />      from users as u <br />    ) as o <br />    cross join <br />    ( <br />     
selectu.id as user_id, u.gender <br />      from users as u <br />      where u.id = 1 <br />    ) as my; <br /><br
/>$$language sql stable; <br /><br />-- WHEN I CALL IT AS A FUNCTION <br /><br />select * from matcher() where user_id
=2; <br /><br />LOG:  duration: 1.242 ms  plan: <br />    Query Text: <br /><br />        select o.user_id, 1 as match
<br/>        from ( <br />          select u.id as user_id, u.gender <br />          from users as u <br />        ) as
o<br />        cross join <br />        ( <br />          select u.id as user_id, u.gender <br />          from users
asu <br />          where u.id = 1 <br />        ) as my; <br /><br /><br />    Nested Loop  (cost=0.00..118.39
rows=1656width=4) (actual time=0.022..0.888 rows=1613 loops=1) <br />      Output: u.id, 1 <br />      ->  Index
Scanusing users_pkey on public.users u  (cost=0.00..8.27 rows=1 width=0) (actual time=0.013..0.015 rows=1 loops=1) <br
/>           Index Cond: (u.id = 1) <br />      ->  Seq Scan on public.users u  (cost=0.00..93.56 rows=1656 width=4)
(actualtime=0.004..0.479 rows=1613 loops=1) <br />            Output: u.id <br />CONTEXT:  SQL function "matcher"
statement1 <br />LOG:  duration: 1.951 ms  plan: <br />    Query Text: select * from matcher() where user_id = 2; <br
/>   Function Scan on public.matcher  (cost=0.25..12.75 rows=5 width=8) (actual time=1.687..1.940 rows=1 loops=1) <br
/>     Output: user_id, match <br />      Filter: (matcher.user_id = 2) <br /><br />-- WHEN I CALL IT AS A SUBQUERY <br
/><br/>select * from <br />( <br />  select o.user_id, 1 as match <br />  from ( <br />    select u.id as user_id,
u.gender<br />    from users as u <br />  ) as o <br />  cross join <br />  ( <br />    select u.id as user_id,
u.gender<br />    from users as u <br />    where u.id = 1 <br />  ) as my <br />) as matcher <br />where user_id = 2;
<br/><br />LOG:  duration: 0.044 ms  plan: <br />    Query Text: select * from <br />    ( <br />      select
o.user_id,1 as match <br />      from ( <br />        select u.id as user_id, u.gender <br />        from users as u
<br/>      ) as o <br />      cross join <br />      ( <br />        select u.id as user_id, u.gender <br />       
fromusers as u <br />        where u.id = 1 <br />      ) as my <br />    ) as matcher <br />    where user_id = 2; <br
/>   Nested Loop  (cost=0.00..16.55 rows=1 width=4) (actual time=0.028..0.031 rows=1 loops=1) <br />      Output: u.id,
1<br />      ->  Index Scan using users_pkey on public.users u  (cost=0.00..8.27 rows=1 width=4) (actual
time=0.021..0.022rows=1 loops=1) <br />            Output: u.id <br />            Index Cond: (u.id = 2) <br />     
-> Index Scan using users_pkey on public.users u  (cost=0.00..8.27 rows=1 width=0) (actual time=0.004..0.006 rows=1
loops=1)<br />            Index Cond: (u.id = 1) <br /><br />-- WHEN I CALL IT AS A VIEW <br /><br />create view
matchviewas <br />select o.user_id, 1 as match <br />  from ( <br />    select u.id as user_id, u.gender <br />    from
usersas u <br />  ) as o <br />  cross join <br />  ( <br />    select u.id as user_id, u.gender <br />    from users
asu <br />    where u.id = 1 <br />  ) as my; <br /><br />select * from matchview where user_id = 2; <br /><br /><br
/>LOG: duration: 0.044 ms  plan: <br />    Query Text: select * from matchview where user_id = 2; <br />    Nested
Loop (cost=0.00..16.55 rows=1 width=4) (actual time=0.028..0.031 rows=1 loops=1) <br />      Output: u.id, 1 <br
/>     ->  Index Scan using users_pkey on public.users u  (cost=0.00..8.27 rows=1 width=4) (actual time=0.021..0.022
rows=1loops=1) <br />            Output: u.id <br />            Index Cond: (u.id = 2) <br />      ->  Index Scan
usingusers_pkey on public.users u  (cost=0.00..8.27 rows=1 width=0) (actual time=0.005..0.007 rows=1 loops=1) <br
/>           Index Cond: (u.id = 1) <br /><br /><hr class="__pbConvHr" style="border: none 0; border-top: 1px dotted
#B5B5B5;height: 
 
1px;margin:15px 0 0 0" /></div></blockquote><br />