Re: Predicates not getting pushed into SQL function?

What other info can I provide? id is int, gender is varchar(255), and it's happening on 9.0.4...

Tom Lane


November 3, 2011 2:41 PM



Jay Levitt jay(dot)levitt(at)gmail(dot)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

Jay Levitt


November 3, 2011 1:47 PM



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)