pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPredicates not getting pushed into SQL function?
6+ messages / 2 participants
[nested] [flat]
* Predicates not getting pushed into SQL function?
@ 2011-11-03 17:47 Jay Levitt <jay.levitt@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jay Levitt @ 2011-11-03 17:47 UTC (permalink / raw)
To: pgsql-performance
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.
I thought SQL functions were generally inline-able, push-down-able, etc. 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. The real function needs parameters, so I can't use it
as a view. Are there better workarounds?
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.
Pretty-printed function and explain analyze results:
https://gist.github.com/1336963
In ASCII for web-haters and posterity:
-- THE OVERLY SIMPLIFIED FUNCTION
create or replace function matcher()
returns table(user_id int, match int) as $$
select o.user_id, 1 as match
from (
select u.id as user_id, u.gender
from users as u
) as o
cross join
(
select u.id as user_id, u.gender
from users as u
where u.id = 1
) as my;
$$ language sql stable;
-- WHEN I CALL IT AS A FUNCTION
select * from matcher() where user_id = 2;
LOG: duration: 1.242 ms plan:
Query Text:
select o.user_id, 1 as match
from (
select u.id as user_id, u.gender
from users as u
) as o
cross join
(
select u.id as user_id, u.gender
from users as u
where u.id = 1
) as my;
Nested Loop (cost=0.00..118.39 rows=1656 width=4) (actual
time=0.022..0.888 rows=1613 loops=1)
Output: u.id, 1
-> Index Scan using 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)
Index Cond: (u.id = 1)
-> Seq Scan on public.users u (cost=0.00..93.56 rows=1656 width=4)
(actual time=0.004..0.479 rows=1613 loops=1)
Output: u.id
CONTEXT: SQL function "matcher" statement 1
LOG: duration: 1.951 ms plan:
Query Text: select * from matcher() where user_id = 2;
Function Scan on public.matcher (cost=0.25..12.75 rows=5 width=8)
(actual time=1.687..1.940 rows=1 loops=1)
Output: user_id, match
Filter: (matcher.user_id = 2)
-- WHEN I CALL IT AS A SUBQUERY
select * from
(
select o.user_id, 1 as match
from (
select u.id as user_id, u.gender
from users as u
) as o
cross join
(
select u.id as user_id, u.gender
from users as u
where u.id = 1
) as my
) as matcher
where user_id = 2;
LOG: duration: 0.044 ms plan:
Query Text: select * from
(
select o.user_id, 1 as match
from (
select u.id as user_id, u.gender
from users as u
) as o
cross join
(
select u.id as user_id, u.gender
from users as u
where u.id = 1
) as my
) as matcher
where user_id = 2;
Nested Loop (cost=0.00..16.55 rows=1 width=4) (actual
time=0.028..0.031 rows=1 loops=1)
Output: u.id, 1
-> 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=1 loops=1)
Output: u.id
Index Cond: (u.id = 2)
-> 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)
Index Cond: (u.id = 1)
-- WHEN I CALL IT AS A VIEW
create view matchview as
select o.user_id, 1 as match
from (
select u.id as user_id, u.gender
from users as u
) as o
cross join
(
select u.id as user_id, u.gender
from users as u
where u.id = 1
) as my;
select * from matchview where user_id = 2;
LOG: duration: 0.044 ms plan:
Query Text: select * from matchview where user_id = 2;
Nested Loop (cost=0.00..16.55 rows=1 width=4) (actual
time=0.028..0.031 rows=1 loops=1)
Output: u.id, 1
-> 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=1 loops=1)
Output: u.id
Index Cond: (u.id = 2)
-> Index Scan using users_pkey on public.users u (cost=0.00..8.27
rows=1 width=0) (actual time=0.005..0.007 rows=1 loops=1)
Index Cond: (u.id = 1)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Predicates not getting pushed into SQL function?
@ 2011-11-03 18:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jay Levitt <jay.levitt@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-11-03 18:41 UTC (permalink / raw)
To: Jay Levitt <jay.levitt@gmail.com>; +Cc: pgsql-performance
Jay Levitt <jay.levitt@gmail.com> writes:
> 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.
> I thought SQL functions were generally inline-able, push-down-able, etc.
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Predicates not getting pushed into SQL function?
@ 2011-11-03 18:49 Jay Levitt <jay.levitt@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jay Levitt @ 2011-11-03 18:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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? 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"><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 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. 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.
<br>
<br>I thought SQL functions were generally inline-able, push-down-able,
etc. 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. 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 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> 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>
<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 as u
<br> where u.id = 1
<br> ) as my;
<br>
<br>
<br> Nested Loop (cost=0.00..118.39 rows=1656 width=4) (actual
time=0.022..0.888 rows=1613 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=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)
(actual time=0.004..0.479 rows=1613 loops=1)
<br> Output: u.id
<br>CONTEXT: SQL function "matcher" statement 1
<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> from users 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.022 rows=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 matchview as
<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>
<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=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.005..0.007 rows=1 loops=1)
<br> 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
* Re: Predicates not getting pushed into SQL function?
@ 2011-11-03 20:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jay Levitt <jay.levitt@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-11-03 20:35 UTC (permalink / raw)
To: Jay Levitt <jay.levitt@gmail.com>; +Cc: pgsql-performance
Jay Levitt <jay.levitt@gmail.com> writes:
> <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? id is int, gender is varchar(255), and it's happening on
> 9.0.4...<br>
> <blockquote style="border: 0px none;"
> [ etc etc ]
Please don't send HTML-only email to these lists.
Anyway, the answer seems to be that inline_set_returning_function needs
some work to handle cases with declared OUT parameters. I will see
about fixing that going forward, but in existing releases what you need
to do is declare the function as returning SETOF some named composite
type, eg
create type matcher_result as (user_id int, match int);
create or replace function matcher() returns setof matcher_result as ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Predicates not getting pushed into SQL function?
@ 2011-11-07 20:13 Jay Levitt <jay.levitt@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Jay Levitt @ 2011-11-07 20:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Please don't send HTML-only email to these lists.
Oops - new mail client, sorry.
> Anyway, the answer seems to be that inline_set_returning_function needs
> some work to handle cases with declared OUT parameters. I will see
> about fixing that going forward, but in existing releases what you need
> to do is declare the function as returning SETOF some named composite
> type
Yes, that patch works great! Oddly enough, the workaround now does NOT
work; functions returning SETOF named composite types don't get inlined, but
functions returning the equivalent TABLE do get inlined. Let me know if you
need a failcase, but the bug doesn't actually affect me now :)
Jay
>
> create type matcher_result as (user_id int, match int);
>
> create or replace function matcher() returns setof matcher_result as ...
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Predicates not getting pushed into SQL function?
@ 2011-11-07 20:15 Jay Levitt <jay.levitt@gmail.com>
parent: Jay Levitt <jay.levitt@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jay Levitt @ 2011-11-07 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Jay Levitt wrote:
> Yes, that patch works great! Oddly enough, the workaround now does NOT work;
> functions returning SETOF named composite types don't get inlined, but
> functions returning the equivalent TABLE do get inlined. Let me know if you
> need a failcase, but the bug doesn't actually affect me now :)
Never mind... I left a "strict" in my test. Works great all around.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-11-07 20:15 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-03 17:47 Predicates not getting pushed into SQL function? Jay Levitt <jay.levitt@gmail.com>
2011-11-03 18:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-03 18:49 ` Jay Levitt <jay.levitt@gmail.com>
2011-11-03 20:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-11-07 20:13 ` Jay Levitt <jay.levitt@gmail.com>
2011-11-07 20:15 ` Jay Levitt <jay.levitt@gmail.com>
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