Created
November 3, 2011 16:27
-
-
Save jaylevitt/1336963 to your computer and use it in GitHub Desktop.
Why doesn't this predicate get pushed down in a function?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select * from matcher() where user_id = 2; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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