Re: [HACKERS] Costs estimates for (inline SQL) functions ...
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Costs estimates for (inline SQL) functions ...
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 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); [EMAIL PROTECTED]:/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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Costs estimates for (inline SQL) functions ...
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
