pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jay Levitt <jay.levitt@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Predicates not getting pushed into SQL function?
Date: Thu, 03 Nov 2011 14:49:53 -0400
Message-ID: <4EB2E251.90906@gmail.com> (raw)
In-Reply-To: <6646.1320345676@sss.pgh.pa.us>
References: <4EB2D3CB.7020708@gmail.com>
	<6646.1320345676@sss.pgh.pa.us>

This is a multi-part message in MIME format.
--------------010002070706050205040200
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html><head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type">
</head><body bgcolor="#FFFFFF" text="#000000">What other info can I 
provide?&nbsp; id is int, gender is varchar(255), and it's happening on 
9.0.4...<br>
<blockquote style="border: 0px none;" 
cite="mid:6646.1320345676@sss.pgh.pa.us" type="cite">
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr">
    <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
 photoaddress="tgl@sss.pgh.pa.us" photoname="Tom Lane" 
src="cid:part1.06030504.03020905@gmail.com" 
name="compose-unknown-contact.jpg" height="25px" width="25px"></div>   <div
 
style="display:table-cell;white-space:nowrap;vertical-align:middle;width:100%">
   	<font color="#737F92"><span 
style="padding-right:6px;font-weight:bold">Tom Lane</span></font></div> 
  <div 
style="display:table-cell;white-space:nowrap;vertical-align:middle;">   
  <font color="#9FA2A5"><span style="padding-left:6px">November 3, 2011 
2:41 PM</span></font></div></div>
  </div>
  <div style="color:#888888;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody"><pre wrap="">Jay Levitt <a class="moz-txt-link-rfc2396E" href="mailto:jay.levitt@gmail.com">&lt;jay.levitt@gmail.com&gt;</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 style="margin:30px 25px 10px 25px;" class="__pbConvHr"><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
 photoaddress="jay.levitt@gmail.com" photoname="Jay Levitt" 
src="cid:part1.06030504.03020905@gmail.com" 
name="compose-unknown-contact.jpg" height="25px" width="25px"></div>   <div
 
style="display:table-cell;white-space:nowrap;vertical-align:middle;width:100%">
   	<font color="#737F92"><span 
style="padding-right:6px;font-weight:bold">Jay Levitt</span></font></div>
   <div 
style="display:table-cell;white-space:nowrap;vertical-align:middle;">   
  <font color="#9FA2A5"><span style="padding-left:6px">November 3, 2011 
1:47 PM</span></font></div></div></div>
  <div style="color:#888888;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody">I'm confused.&nbsp; I have a 
now-trivial SQL function that, unrestricted, would 
scan my whole users table.&nbsp; When I paste the body of the function as a 
subquery and restrict it to one row, it only produces one row.&nbsp; When I 
paste 
the body of the function into a view and restrict it to one row, it 
produces 
one row.&nbsp; But when I put it in a SQL function... it scans the whole 
users 
table and then throws the other rows away.
<br>
<br>I thought SQL functions were generally inline-able, push-down-able, 
etc.&nbsp; As 
a workaround, I can put my WHERE clause inside the function and pass it 
parameters, but that feels ugly, and it won't help for things like 
resticting via JOINs.&nbsp; The real function needs parameters, so I can't 
use it 
as a view.&nbsp; Are there better workarounds?
<br>
<br>I suspect the problem is (something like) the planner doesn't 
realize the 
function will produce a variable number of rows; I can specify COST or 
ROWS, 
but they're both fixed values.
<br>
<br>Pretty-printed function and 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_id int, match int) as $$
<br>
<br>&nbsp;&nbsp;&nbsp; select o.user_id, 1 as match
<br>&nbsp;&nbsp;&nbsp; from (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp; ) as o
<br>&nbsp;&nbsp;&nbsp; cross join
<br>&nbsp;&nbsp;&nbsp; (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where u.id = 1
<br>&nbsp;&nbsp;&nbsp; ) 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:&nbsp; duration: 1.242 ms&nbsp; plan:
<br>&nbsp;&nbsp;&nbsp; Query Text:
<br>
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select o.user_id, 1 as match
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) as o
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cross join
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where u.id = 1
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) as my;
<br>
<br>
<br>&nbsp;&nbsp;&nbsp; Nested Loop&nbsp; (cost=0.00..118.39 rows=1656 width=4) (actual 
time=0.022..0.888 rows=1613 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: u.id, 1
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using users_pkey on public.users u&nbsp; 
(cost=0.00..8.27 
rows=1 width=0) (actual time=0.013..0.015 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (u.id = 1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on public.users u&nbsp; (cost=0.00..93.56 rows=1656
 width=4) 
(actual time=0.004..0.479 rows=1613 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: u.id
<br>CONTEXT:&nbsp; SQL function "matcher" statement 1
<br>LOG:&nbsp; duration: 1.951 ms&nbsp; plan:
<br>&nbsp;&nbsp;&nbsp; Query Text: select * from matcher() where user_id = 2;
<br>&nbsp;&nbsp;&nbsp; Function Scan on public.matcher&nbsp; (cost=0.25..12.75 rows=5 
width=8) 
(actual time=1.687..1.940 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: user_id, match
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (matcher.user_id = 2)
<br>
<br>-- WHEN I CALL IT AS A SUBQUERY
<br>
<br>select * from
<br>(
<br>&nbsp; select o.user_id, 1 as match
<br>&nbsp; from (
<br>&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp; ) as o
<br>&nbsp; cross join
<br>&nbsp; (
<br>&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp; where u.id = 1
<br>&nbsp; ) as my
<br>) as matcher
<br>where user_id = 2;
<br>
<br>LOG:&nbsp; duration: 0.044 ms&nbsp; plan:
<br>&nbsp;&nbsp;&nbsp; Query Text: select * from
<br>&nbsp;&nbsp;&nbsp; (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select o.user_id, 1 as match
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) as o
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cross join
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where u.id = 1
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) as my
<br>&nbsp;&nbsp;&nbsp; ) as matcher
<br>&nbsp;&nbsp;&nbsp; where user_id = 2;
<br>&nbsp;&nbsp;&nbsp; Nested Loop&nbsp; (cost=0.00..16.55 rows=1 width=4) (actual 
time=0.028..0.031 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: u.id, 1
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using users_pkey on public.users u&nbsp; 
(cost=0.00..8.27 
rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: u.id
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (u.id = 2)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using users_pkey on public.users u&nbsp; 
(cost=0.00..8.27 
rows=1 width=0) (actual time=0.004..0.006 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (u.id = 1)
<br>
<br>-- WHEN I CALL IT AS A VIEW
<br>
<br>create view matchview as
<br>select o.user_id, 1 as match
<br>&nbsp; from (
<br>&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp; ) as o
<br>&nbsp; cross join
<br>&nbsp; (
<br>&nbsp;&nbsp;&nbsp; select u.id as user_id, u.gender
<br>&nbsp;&nbsp;&nbsp; from users as u
<br>&nbsp;&nbsp;&nbsp; where u.id = 1
<br>&nbsp; ) as my;
<br>
<br>select * from matchview where user_id = 2;
<br>
<br>
<br>LOG:&nbsp; duration: 0.044 ms&nbsp; plan:
<br>&nbsp;&nbsp;&nbsp; Query Text: select * from matchview where user_id = 2;
<br>&nbsp;&nbsp;&nbsp; Nested Loop&nbsp; (cost=0.00..16.55 rows=1 width=4) (actual 
time=0.028..0.031 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: u.id, 1
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using users_pkey on public.users u&nbsp; 
(cost=0.00..8.27 
rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Output: u.id
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (u.id = 2)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using users_pkey on public.users u&nbsp; 
(cost=0.00..8.27 
rows=1 width=0) (actual time=0.005..0.007 rows=1 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (u.id = 1)
<br>
<br><hr style="border: none 0; border-top: 1px dotted #B5B5B5; height: 
1px;margin:15px 0 0 0" class="__pbConvHr"> </div>
</blockquote>
<br>
</body></html>
--------------010002070706050205040200
Content-Type: image/jpeg; x-apple-mail-type=stationery;
	name="compose-unknown-contact.jpg"
Content-Transfer-Encoding: base64
Content-ID: <part1.06030504.03020905@gmail.com>
Content-Disposition: inline;
 filename="compose-unknown-contact.jpg"

/9j/4AAQSkZJRgABAQEARwBHAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEC
AQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEBAQEBAQICAgICAgIC
AgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/wAAR
CAAZABkDAREAAhEBAxEB/8QAGAAAAwEBAAAAAAAAAAAAAAAABgcICQr/xAA0EAABAwMCAgUK
BwAAAAAAAAACAQMEBQYRABITIQcUMUF2CBUXIjI2N0JRtVRWkZOV0dL/xAAYAQEAAwEAAAAA
AAAAAAAAAAADAAEEAv/EACQRAAICAAQGAwAAAAAAAAAAAAABAhEDMrHREyExM0FxgfDx/9oA
DAMBAAIRAxEAPwDuEt+gW/ULet6oVC3rfqNQqFv0OfPn1GhUqfOmzZtKZlS5UqZMaNwzNwiJ
VIl7eXLCaZIGwBl3TY8epPx2+jy2ZNPjvkwc9uhW8j7nCPhvOsQliYIeS7cvCpp8o50qwrC4
v3lsNSDbdmTEhvs2tahxpfV3WnmbbozJEw/gwdadbYExVRXKEKoSdvJcaOSqxE7/AAiX0gXx
+a69/JSf9alIlste0VzaNpeFrcT9KKymotyiaZ0KRCnzacoE7Kjzn4gi2KqUh3jqDHDHv4mR
UfruTWlMzlVUKIVNp9GguEJnAh0+IZjyAiisgyRDnu5azS8miKqjOTVkKqS/psG37fo1Fbab
eg25b8eZPeFJBBJSjMG5HjMeyihnaauZwe4OGiju13GAcpOwBeN+U8/IkGbsiS8b7ryogmbz
hbyc9REROfZhERO5ETShjPtvpGqTUyLErytS4siSwx5x2tRH4hPOI0DkjZtaJtFxuVEbIUUi
yeNujlBUJGbJN6nM/Cyf2Hf60YgjvKA+NPSP4gT7axpcPtr51YWJnYn9dnAQWl722p4ot37y
zqnlfp6FrqbwawG8/9k=
--------------010002070706050205040200--



Attachments:


compose-unknown-contact.jpg (image/jpeg, 770B) download
  view image

view thread (6+ messages)  latest in thread

Message-ID: <4EB2E251.90906@gmail.com>
Permalink:  ../4EB2E251.90906@gmail.com/
Also on:    postgresql.org/message-id/4EB2E251.90906@gmail.com

 · 

reply
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-performance@postgresql.org
  Cc: jay.levitt@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: Predicates not getting pushed into SQL function?
  In-Reply-To: <4EB2E251.90906@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