Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: vernonw@gatewaytech.com
Subject: Re: query speed joining tables
Date: Tue, 14 Jan 2003 11:38:43 -0800
Cc: pgsql-sql@postgresql.org
References: <LK4WXSGCFVTCBB9ZUOI71542NHVUJE.3e25a967@kimiko>
In-Reply-To: <LK4WXSGCFVTCBB9ZUOI71542NHVUJE.3e25a967@kimiko>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200301141138.43029.josh@agliodbs.com>


Vernon,

> What I stated is my observation on my project with over twenty multivalue=
d=20
detail tables. I have a selection query=20
> contained 200 characters, involving 10 tables, and using subquery. The=20
performance is not bad after properly indexing,=20
> least than 3 second (what the planner says). I will have longer queries=
=20
later and hope they won't have any performance=20
> problem.

One trick for you is to create a custom aggregate for string contination fo=
r=20
each detail table, and that will allow you to list the values in the detail=
=20
table as if they were a continuous text string.   A concat aggregate is eve=
n=20
fast on PostgreSQL.


CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 =3D '''' THEN $1
	WHEN $1 IS NULL OR $1 =3D '''' THEN $2
	ELSE $1 || '', '' || $2
	END
' LANGUAGE 'sql';

CREATE FUNCTION "br_cat" (text, text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 =3D '''' THEN $1
WHEN $1 IS NULL OR $1 =3D '''' THEN $2
ELSE $1 || ''<br>'' || $2
END
' LANGUAGE 'sql';

--create aggregate with html <breaks> between items
CREATE AGGREGATE br_list ( BASETYPE =3D text, SFUNC =3D br_cat, STYPE =3D t=
ext,=20
INITCOND =3D '' );

--create aggregate with commas between items
CREATE AGGREGATE comma_list ( BASETYPE =3D text, SFUNC =3D comma_cat, STYPE=
 =3D=20
text,
INITCOND =3D '' );

-Josh Berkus