Thread: How does the planner treat a table function.

Hi,
we have got some tables (uw?) and functions. One function is defined like

get_abc():
SELECT a,b,c from table_x;

What happens if I query something like  
SELECT a,b from get_abc() where a=5;
while table_x is rather big?


Will PSQL at first query all records of table_x and then apply a where a=5
OR
will PSQL integrate it to a shorter query?


(In case there will be of course an intex over a to prevent sequential
search)

If you are interested in performance things, you may use the EXPLAIN
command:

explain select * from "GetLanguages"() where "Id" > 44

vs.

explain select * from "Languages" where "Id">44;

However the explain stops at the point a function is invoked.

This may mean that (as you asked) a select over a table function produces a
temprary table and thus costs much ressources if the table is big.

|-----Original Message-----
|From: KÖPFERL Robert
|Sent: Montag, 14. März 2005 12:26
|To: pgsql-sql@postgresql.org
|Subject: [SQL] How does the planner treat a table function.
|
|
|
|Hi,
|we have got some tables (uw?) and functions. One function is
|defined like
|
|get_abc():
|SELECT a,b,c from table_x;
|
|What happens if I query something like
|SELECT a,b from get_abc() where a=5;
|while table_x is rather big?
|
|
|Will PSQL at first query all records of table_x and then apply
|a where a=5
|OR
|will PSQL integrate it to a shorter query?
|
|
|(In case there will be of course an intex over a to prevent sequential
|search)
|
|---------------------------(end of
|broadcast)---------------------------
|TIP 9: the planner will ignore your desire to choose an index
|scan if your
|      joining column's datatypes do not match
|