[SQL] 'cost' and 'rows' for volitile function
pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly VOLATILE functions. Is one hundred the new ninety-nine? and therefore these values are ignored by the planner? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 'cost' and 'rows' for volitile function
Rob Sargent wrote: Richard Huxton wrote: Rob Sargent wrote: pg-admin is showing 'COST 100' and 'ROWS 1000' for my explicitly VOLATILE functions. Thanks for the pointer back to CREATE FUNCTION docs. That would have told me that those value are indeed the defaults. I guess I'm somewhat surprised that they are in fact defautled. COST is too arbitrary for me to comment on, but my functions' results sets fall on both sides of 1000 rows, so I'm not sure giving this value to the planner does a lot of good. Luckily, my functions are typically pretty much stand-alone calls, and not part of some larger query. If your functions are all called at the top-level then indeed it doesn't matter. At a low level though, telling the planner function F1 costs 1000 times more than F2 is useful. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 'cost' and 'rows' for volitile function
Rob Sargent wrote: Richard Huxton wrote: If your functions are all called at the top-level then indeed it doesn't matter. At a low level though, telling the planner function F1 costs 1000 times more than F2 is useful. What scares me about this is that for function such as the ones I'm currently using which return a wide range of result set size depending on input values. The planner will be mis-informed by a factor of 10 or more quite easily. Well, like I said - it doesn't matter as long as you are calling the function at the top level. The only option the planner has is execute function. However, it is a real problem for what you might term irregularly shaped functions where the costs/rows varies widely. I seem to recall a suggestion at one point that the values could be replaced by cost functions that get the same parameters as the function itself. However, that's could be expensive to do for every function-call, and especially since there's not always an obvious way to estimate # rows without calling the function first. I daresay someone will get around to handling both options in a clean way at some point, but the ability to define function costs at all was only added in 8.2 iirc. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Xml Schemas
On Saturday 06 June 2009 22:53:19 Isaac Dover wrote: Hello, I am attempting to build an xml representation of any database, but I'm having trouble doing so. I was interested in using the existing xml functions, such as schema_to_xmlschema, but the results are strange and unusable. The resulting schema doesn't even contain the column names, though it doesn't include the table names. Often, the type information is incomplete as well. Are there any examples for using this family of functions, schema_to_xmlschema, etc? My end goal is any xml representation of the database that includes type information. It appears to me that there is some kind of bug in schema_to_xmlschema. As you say, it misses a bunch of information, such as the column names of the tables. As far as I can tell, the SQL standard actually defines it that way, but that must be a bug. If you use table_to_xmlschema, you will get the proper output, separately for each table. Note, however, that the XML mapping is not designed to be reversible. Depending on what you want to do, this may not be the right format for you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
