Re: drawback of array vs join

On Tue, 23 Jun 2009 17:39:46 -0300
Emanuel Calvo Franco <postgres(dot)arg(at)gmail(dot)com> wrote:

> 2009/6/23 Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>:
> > I've a temporary table where I'd like to resume data coming from
> > several other tables (normalised the usual way).
> > eg.
> > a list of items that may belong to several group (eg. item,
> > group, itemgroup table) will end up in something similar to:
> >
> > create temp table itemlisttemp(
> >  lid int,
> >  iid int,
> >  qty int,
> >  famid int,
> >  rating int,
> >  itemgroups int[],
> > );
> >
> > This because I've to loop over the same table several times and
> > because it *should* make easier to write dynamic queries [built
> > from a web interface] like
> >
> > select sum(qty) from where
> >  famid=3
> >  and rating>0
> >  and 10 = any itemgroups
> > ...
> >
> > itemlisttemp tables will be very small (30 as an upper limit) and
> > arrays as well will contain very few elements (4 as an upper
> > limit).

> Which improvement do you want to obtain with the array way?
> If the table is very small, the improvement in terms of performance
> will be negligible.

I'll have to compute different kind of aggregates over itemlisttemp
and update it as well, but while itemlisttemp will be small
the tables from which it is derived aren't.

Keeping the join will make appear as many row as "groups", that will
make harder to understand and build the queries that will be used to
build up the aggregates.

Furthermore if I skip the condition on group, I'll have to skip
the corresponding join otherwise I'll have a row for each group the
item belongs to.

select sum(l.qty) from list l
join groupitem gi on l.iid=gi.iid
where
l.famid=3
and rating>0
and gi.gid=10
;

select sum(l.qty) from list l
join groupitem gi on l.iid=gi.iid
where
l.famid=3
and rating>0
; -- oops this is wrong

compared to:

select sum(qty) from where
famid=3
and rating>0
and 10 = any itemgroups
;
select sum(qty) from where
famid=3
and rating>0
;

I'm still collecting what I really need in itemlisttemp but I'm
expecting no more than 3-4 arrays in itemlisttemp for a very long
time.
Since I've to fill the arrays just one time... and compute
aggregates with different conditions and update itemlisttemp I'm
doing it mostly for readability and ease of building the statements
dynamically.

I was wondering if I could run in any other drawback other than not
doing it the halal relational way.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it