Costs estimates for (inline SQL) functions ...
As my last mail did not seem to go through here one more try ...
When looking at some fairly complex SQL stuff I came across some
interesting issue which is a bit surprising to me:
CREATE OR REPLACE FUNCTION xy() RETURNS SETOF record AS $$
SELECT relname::text, relpages::int4
FROM pg_class;
$$ LANGUAGE SQL IMMUTABLE;
explain SELECT * FROM xy() AS (relname text, relpages int4);
hs@athlon64:/tmp$ psql test < check.sql
CREATE FUNCTION
QUERY PLAN
------------------------------------------------------------
Function Scan on xy (cost=0.00..12.50 rows=1000 width=36)
(1 row)
As far as i remember inlined SQL code has been implemented into the
planner around 7.4.
This should also be true according to ...
http://conferences.oreillynet.com/cs/os2003/view/e_sess/4372
In my theory the function is inlined and therefore we should see
different costs here (the ones of the "real" query).
Does anybody happen to know more about this issue?
Many thanks,
hans
--
Cybertec Geschwinde & Sch�nig GmbH
Sch�ngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
On Mon, 2006-08-21 at 07:31 +0200, Hans-Juergen Schoenig wrote:
CREATE OR REPLACE FUNCTION xy() RETURNS SETOF record AS $$
SELECT relname::text, relpages::int4
FROM pg_class;
$$ LANGUAGE SQL IMMUTABLE;
As far as i remember inlined SQL code has been implemented into the
planner around 7.4.
We don't currently inline set-returning functions. This has been on my
TODO list for a while, but I haven't found the cycles for it yet (of
course, if anyone's interested in fixing this, don't let my idleness
stop you -- I would think it shouldn't be too difficult).
-Neil
On Sun, Aug 20, 2006 at 10:56:12PM -0700, Neil Conway wrote:
On Mon, 2006-08-21 at 07:31 +0200, Hans-Juergen Schoenig wrote:
CREATE OR REPLACE FUNCTION xy() RETURNS SETOF record AS $$
SELECT relname::text, relpages::int4
FROM pg_class;
$$ LANGUAGE SQL IMMUTABLE;As far as i remember inlined SQL code has been implemented into the
planner around 7.4.We don't currently inline set-returning functions. This has been on my
TODO list for a while, but I haven't found the cycles for it yet (of
course, if anyone's interested in fixing this, don't let my idleness
stop you -- I would think it shouldn't be too difficult).
We should put this on the project's TODO...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote:
On Sun, Aug 20, 2006 at 10:56:12PM -0700, Neil Conway wrote:
On Mon, 2006-08-21 at 07:31 +0200, Hans-Juergen Schoenig wrote:
CREATE OR REPLACE FUNCTION xy() RETURNS SETOF record AS $$
SELECT relname::text, relpages::int4
FROM pg_class;
$$ LANGUAGE SQL IMMUTABLE;As far as i remember inlined SQL code has been implemented into the
planner around 7.4.We don't currently inline set-returning functions. This has been on my
TODO list for a while, but I haven't found the cycles for it yet (of
course, if anyone's interested in fixing this, don't let my idleness
stop you -- I would think it shouldn't be too difficult).We should put this on the project's TODO...
Done.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +