Skip site navigation (1) Skip section navigation (2)

Help request to improve function performance

From: sarikan <serefarikan(at)kurumsalteknoloji(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Help request to improve function performance
Date: 2009-04-22 16:08:43
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Dear members of the list, 
I have a function which returns a custom type, that has only two fields,
each of them being varchar arrays. 
The reason that I have written this function is that I have a table
basically with the following structure (with simplified column names)

name_col1  name_col2 sessionId
value1       value3       id1
value2       value2       id1
value4       value4       id1
value7       value4       id2
value2       value2       id2
value4       value4       id2
value1       value5       id3

So mutliple rows are bound together with sessionIds, and I need to get back
all rows with a query, grouped by sessionID. However, group by sql statement
does not solve my problem, since I get back a lot of rows, which I have to
group into objects again in my application. What I need is a way to return
all rows having the same sessionId as a single row. Of course this is not
possible with this table, so I've created a custom type, which has array
type columns. The following function gets all rows that belongs to a
patient, and for each session id, it inserts rows with that session id into
array fields of the custom type. 
The problem is, it is very slow! Getting back all the rows with a select
takes 360 ms, while getting back the results of this function takes 50
seconds! Is there any way I can make the following function faster, or any
other methods you can recommend to do what I'm trying to do?  I am trying to
avoid hundreds of calls to db, or grouping query results in my middleware
application. Here comes the function, and your help will be much

Best Regards

CREATE or REPLACE FUNCTION getNodeContainers( context_Id varchar) RETURNS
setof NodesContainer AS
archetype_data_row app.archetype_data%ROWTYPE;
archetype_data_row_main app.archetype_data%ROWTYPE;
nodescontainervar NodesContainer%ROWTYPE;
session_Id varchar;
indexVar integer := 0;
    CREATE TEMP TABLE all_rows_of_patient AS select * from
    WHERE app.archetype_data.context_id = context_Id;
    FOR session_Id IN
      SELECT distinct(all_rows_of_patient.session_id) from
    LOOP -- do the following for each session_ID	
	indexVar := 0;	
	FOR archetype_data_row IN --select rows that belong to this session ID 
		SELECT * from all_rows_of_patient
	WHERE all_rows_of_patient.session_id = session_Id and
all_rows_of_patient.context_id = context_Id
	LOOP[indexVar] :=;   
		nodescontainervar.context_Id[indexVar] := archetype_data_row.context_Id;
		indexVar := indexVar + 1;	
	return NEXT nodescontainervar;
    drop table all_rows_of_patient;
$$ LANGUAGE 'plpgsql';

View this message in context:
Sent from the PostgreSQL - general mailing list archive at


pgsql-general by date

Next:From: rwadeDate: 2009-04-22 16:29:51
Subject: Dynamic SQL in Function
Previous:From: Christine PennerDate: 2009-04-22 15:57:25
Subject: Error installing Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group