pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedArrays Design Question
3+ messages / 2 participants
[nested] [flat]
* Arrays Design Question
@ 2002-03-26 08:12 Achilleus Mantzios <rnd@gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleus Mantzios @ 2002-03-26 08:12 UTC (permalink / raw)
To: pgsql-sql
Hi,
i am wondering about what could be the best design practice regarding
performance for the following problem:
I want to store the factors of a linear formula of unknown degree (but less
than 10) in a table for online stats/graphs on historical data (about 200 Mb).
I am thinking of
a) Store each formula as a float8[] array and then write a pgsql function in C
to compute the result given the value of variable x.
b) Add 10 columns of type float8 and store 0 values where the position of the
column exceeds the degree of the formula.
The array implementation seems more simple and more neat since it has no
assumptions about the maximal degree, but it is not standard SQL,
the JDBC might have problems with array types, and i dont know the performance
implications.
Could some one please give a point to read about arrays implementation in
postgresql, their performance, accessing arrays from server pgsql C
functions??
Thanx
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: it@dynacomtm.com
rnd@gatewaynet.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Arrays Design Question
@ 2002-03-26 16:44 Josh Berkus <josh@agliodbs.com>
parent: Achilleus Mantzios <rnd@gatewaynet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-03-26 16:44 UTC (permalink / raw)
To: Achilleus Mantzios <rnd@gatewaynet.com>; pgsql-sql
Achilleus ,
> performance for the following problem:
> I want to store the factors of a linear formula of unknown degree
> (but less
> than 10) in a table for online stats/graphs on historical data (about
> 200 Mb).
> I am thinking of
> a) Store each formula as a float8[] array and then write a pgsql
> function in C
> to compute the result given the value of variable x.
> b) Add 10 columns of type float8 and store 0 values where the
> position of the
> column exceeds the degree of the formula.
BOTH of the above are poor approaches that will lead to headaches for
you later on. Instead:
c. Store each formula in a column in another table with a parent-child
relationship to your main table, i.e.
online_stats stat_formulas
--------------- ------------------
online_stat_id ------------->online_stat_id
formula_number
formula
This is basic relational database design. You should probably pick up
a copy of "Relational Database Design for Mere Mortals" before you
get yourself into a real mess ...
-Josh Berkus
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Arrays Design Question
@ 2002-03-27 07:23 Achilleus Mantzios <rnd@gatewaynet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2002-03-27 07:23 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
On Wednesday 27 March 2002 09:09, you wrote:
> Achilleus ,
>
> > performance for the following problem:
> > I want to store the factors of a linear formula of unknown degree
> > (but less
> > than 10) in a table for online stats/graphs on historical data (about
> > 200 Mb).
^^^
Dear josh "for" does not mean "in".
> > I am thinking of
> > a) Store each formula as a float8[] array and then write a pgsql
> > function in C
> > to compute the result given the value of variable x.
Average performer.
> > b) Add 10 columns of type float8 and store 0 values where the
> > position of the
> > column exceeds the degree of the formula.
>
Worst performer.
The best performer is do the calculations in java using the "arrays" way.
(BTW pgsql Arrays are hated and used by everone it seems)
> BOTH of the above are poor approaches that will lead to headaches for
> you later on. Instead:
>
> c. Store each formula in a column in another table with a parent-child
^^^^ ^^^^^^^ ^ ^^^^^^
That was the question actually.
> relationship to your main table, i.e.
>
> online_stats stat_formulas
> --------------- ------------------
> online_stat_id -------------> online_stat_id
> formula_number
> formula
>
> This is basic relational database design. You should probably pick up
> a copy of "Relational Database Design for Mere Mortals" before you
> get yourself into a real mess ...
>
> -Josh Berkus
How did you conclude i was about to store the formulae in the stats table?
Is my English that bad?
:))
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: it@dynacomtm.com
rnd@gatewaynet.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-03-27 07:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-26 08:12 Arrays Design Question Achilleus Mantzios <rnd@gatewaynet.com>
2002-03-26 16:44 ` Josh Berkus <josh@agliodbs.com>
2002-03-27 07:23 Re: Arrays Design Question Achilleus Mantzios <rnd@gatewaynet.com>
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