Skip to content

Instantly share code, notes, and snippets.

@jaylevitt
Created November 3, 2011 16:27
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jaylevitt/1336963 to your computer and use it in GitHub Desktop.
Save jaylevitt/1336963 to your computer and use it in GitHub Desktop.
Why doesn't this predicate get pushed down in a 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;
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)
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)
-- It gets pushed down in a view, too!
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)
@jaylevitt
Copy link
Author

The answer from Tom Lane, for all you Googlers:

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 ...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment