pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: vernonw@gatewaytech.com, <pgsql-sql@postgresql.org>
Subject: Re: Fwd: Re: Can I search for an array in csf?
Date: Tue, 22 Oct 2002 11:03:56 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <200210212002.52843.josh@agliodbs.com>

On Mon, 21 Oct 2002, Josh Berkus wrote:

>
> Vernon,
>
> > >> One field of a table stores an array of characters in a string fromat as
> > >> "a,b,c,d". Is anyway to apply a select statement without using stored
> > >> procedure?
>
> > The reason I use this format for an array is that the array is dynamic. I
> have quite few cases of this type of situation. The
> > maximize length in some cases is known, is unknown in others. I have learnt
> the comment separated format is one way
> > to solve the problem. Someone also suggested to store the array as an
> object. I am not sure whether it works or not.
> > The application is written in Java, by the way.
>
> You should store this data in a sub-table linked through a foriegn key.
> Period.   Messing with arrays will only lead you to heartache ...

It depends.
I can tell you of situations that doing it with child tables
will hurt performance really bad.
Its just a matter of complexity.

One of the apps we run over here, deals with bunker
analysis of the vessels of our fleet.

For each vessel there are 4 formulas that describe the parameters of
the consumption of fuel oil under some given conditions.

I have implemented this using arrays.
The app is written in J2EE.

On a dual xeon 2.2 GHz with 1 GB for postgres,
it takes about 900 miliseconds to compute
some statistics (average, std deviation,etc..)
of the consumption of all vessels (about 20 of them)
for a period of 3 years (the values are stored for each day).

Before going with the formulas, we had a rather
primitive scheme originated from the previous
cobol application, based on subtable look ups,
(and there was no serious computations involved
just table lookups).

I can tell you the performance boost was remarkable.

>
> Try the book "Database Design For Mere Mortals" for a primer on SQL DB design.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr




view thread (9+ messages)  latest in thread

Message-ID: <Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com>
Permalink:  ../Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com, josh@agliodbs.com
  Subject: Re: Fwd: Re: Can I search for an array in csf?
  In-Reply-To: <Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com>
  References: <200210212002.52843.josh@agliodbs.com> <Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.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