[GENERAL] Table Column Retrieval
Recently I read that one of the distinctions between a standard database and a columnar one, which led to an increase in its efficiency, was and I quote: "Only relevant columns are retrieved (A row-wise database would pull all columns and typically discard 80-95% of them)" Is this true of PostgreSQL? That eventhough my query does not call for a column it is still pulled from the table row(s). I know that my client via the JDBC does not contain the data in the ResultSet for the column, because of the packet monitoring I have done on queries. danap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pain of postgres upgrade with extensions
I noticed this immediately when using the PostgreSQL tool for examples of dumps for creating export of database/table structure/data for the MyJSQLView application. I considered implementing a similar copy dump, but seems it would not be handled properly with SQL statements. danap. This is not a flame about current or previous release of Postgres. I have just gone through the awful experience of upgrading from Postgres 8.2 to 8.3 with a database that had one of the many Postgres extensions included. The problem comes down to the way that Postgres extensions are packaged up, each extension tends to define some extension specific functions, when you do a dump of the database these functions get include. If upgrade from one version of Postgres to another, you take a dump of the database, which then needs to be upgrade if there have been any changes in the extension. The problem being that there doesn’t seem to be a way of dumping the database with out including extension specific information. There is a possible solution to this problem, move all the extension specific functions to an extension specific schema. That way the contents of the database are kept separate from extensions. For example the postgis function area would change to postgis.area assuming the the schema for postgis extension was call postgis, this would also avoid the problem if two extensions happen to have a function with the same name. D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how do you write aggregate function
Justin, I'm really not a expert in this area, on how to code this, or functions in PostgreSQL. All I could offer is some ideas which you might try. Sometimes this is all it takes. Perhaps someone else will respond that might be more helpful. With that said I have read in the documentation the use of other languages and if you are looking for a higher performance, that is the way I would said its going to come about perhaps. danap. DMP you did give me an idea on changing how to call the append array sfunc looks like this create or replace function wcost_average_sf (numeric[], numeric, numeric) returns numeric[] as $Body$ begin return array_append(array_append($1, $2), $3); end; $Body$ LANGUAGE 'plpgsql' VOLATILE; this yanked out 140,000 ms on the run time, a big improvement but no where i want it to be. are there speed improvements in the other languages TCL dmp wrote: Array appends are usually a performance hit, as you said. I'm not sure though with PostgreSQL. Why not try it with two arrays and see what happens. At least you would reducing the single array and the eliminating the append. danap. I got the aggregate function for weighted average done. I finely left alone for more than 10 minutes to actual get it written. It takes 2 value input Weight and the Value. it will sum the weighted entries to create the constant then does the normal formula, but does not percentage number but averaged number. A simple change on the return line it can do percentages. I did a couple of things a little odd . instead of doing a multi dimensional array i did a one dimensional array where the 1st row is Weight and the 2nd row is Value. This made the loop through the array look stupid. I tested it across 50,000 records with a group by it took 3.3 seconds to run. without the group by clause performance is terrible taking several minutes just to do the sfunc part. 371,563ms The Array seems to have performance hit any advice?It could be the way i'm appending to the Array which has a performance hit as the array gets bigger and bigger ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how do you write aggregate function
Array appends are usually a performance hit, as you said. I'm not sure though with PostgreSQL. Why not try it with two arrays and see what happens. At least you would reducing the single array and the eliminating the append. danap. I got the aggregate function for weighted average done. I finely left alone for more than 10 minutes to actual get it written. It takes 2 value input Weight and the Value. it will sum the weighted entries to create the constant then does the normal formula, but does not percentage number but averaged number. A simple change on the return line it can do percentages. I did a couple of things a little odd . instead of doing a multi dimensional array i did a one dimensional array where the 1st row is Weight and the 2nd row is Value. This made the loop through the array look stupid. I tested it across 50,000 records with a group by it took 3.3 seconds to run. without the group by clause performance is terrible taking several minutes just to do the sfunc part. 371,563ms The Array seems to have performance hit any advice?It could be the way i'm appending to the Array which has a performance hit as the array gets bigger and bigger ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Changing column names in tables 2
Hello Tony, Perhaps this will help maybe not. Since the original data import used quotes for the fields then any case was maintained in the creation of the new table(s) & fields(s). Just take your original import file and remove all the quotes, ",. Re-import and and PostgreSQL will use all lowercase for the table(s) and field(s). If psql is to much of a hassle, Just use MyJSQLView to dump the database and removed the quotes, ". You should of been able to remove the quotes directly in MyJSQLView during the dump, but I just tried it and it is broken. Edit | Preference | Data Export | SQL, set empty string identifier. I always recommend backing up data to get back where you started just in case things get screwed up, so pg_dump everything before starting. danap. Tony Cade wrote: There are too many fields to issue alter table commands to rename in SQL so my question is , is it safe to use a query such as select relfilenode from pg_class where relname='rates' update pg_attribute set attname=lower(attname) where attnum >0 and attrelid= ( from above query) Don't do that. It's far better to create a shell script, or PL/pgSQL function, whatever suits you, to get the table/column names from the catalog and then produce the ALTER TABLE commands you need. Playing directly with the catalogs is *never* supposed. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL Array Use
I was slightly confused. I'm really trying to identify what type of support the project should provide to array types. Just from the input so far, it looks like more needs to be done. If 100's to 1000's of elements are in an array type, the application is going to have some problems. Presently it will adequately handle less then 10 or 20 elements in multi-dimensional arrays, without much problem with adding, editing, & viewing, but 100s to 1000s of elements need a more robust & easy method to view and edit elements. The data type of the array is not a real problem, except bytea and some of the geometry types which have some specific input form requirements. Text might be a problem if the byte size is large. danap. On Mar 3, 2008, at 2:05 PM, Josh Trutwin wrote: On Mon, 3 Mar 2008 20:48:55 +0100 Karsten Hilbert <[EMAIL PROTECTED]> wrote: On Mon, Mar 03, 2008 at 01:22:17PM -0600, Erik Jones wrote: Where are you getting this information. IMO the OP wanted to know how people *use* arrays, not how one *can* use arrays. That was my thought, sort of a poll. Hopefully OP isn't confused now. :) Ah, I sure was then :) Erik Jones ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL Array Use
Hello, The project MyJSQLView will provided basic support for array types in PostgreSQL at the next release. Information is desired from anyone that uses arrays in PostgreSQL to effect this support. Just a couple of questions. 1. What Size, <10 or 100's, 1000's of elements? 2. Single or Multi-Dimensional? 3. What data types? Any input would be welcome. Thanks, danap. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
