From: Josh Berkus <josh@agliodbs.com>
To: vernonw@gatewaytech.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
Date: Tue, 14 Jan 2003 11:38:43 -0800
Message-ID: <200301141138.43029.josh@agliodbs.com> (raw)
In-Reply-To: <LK4WXSGCFVTCBB9ZUOI71542NHVUJE.3e25a967@kimiko>
References: <LK4WXSGCFVTCBB9ZUOI71542NHVUJE.3e25a967@kimiko>
Vernon,
> What I stated is my observation on my project with over twenty multivalued
detail tables. I have a selection query
> contained 200 characters, involving 10 tables, and using subquery. The
performance is not bad after properly indexing,
> least than 3 second (what the planner says). I will have longer queries
later and hope they won't have any performance
> problem.
One trick for you is to create a custom aggregate for string contination for
each detail table, and that will allow you to list the values in the detail
table as if they were a continuous text string. A concat aggregate is even
fast on PostgreSQL.
CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL OR $2 = '''' THEN $1
WHEN $1 IS NULL OR $1 = '''' 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 = '''' THEN $1
WHEN $1 IS NULL OR $1 = '''' THEN $2
ELSE $1 || ''<br>'' || $2
END
' LANGUAGE 'sql';
--create aggregate with html <breaks> between items
CREATE AGGREGATE br_list ( BASETYPE = text, SFUNC = br_cat, STYPE = text,
INITCOND = '' );
--create aggregate with commas between items
CREATE AGGREGATE comma_list ( BASETYPE = text, SFUNC = comma_cat, STYPE =
text,
INITCOND = '' );
-Josh Berkus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, vernonw@gatewaytech.com
Subject: Re: query speed joining tables
In-Reply-To: <200301141138.43029.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox