Re: Is good idea an array of 365 elements in a cell of a table, in order to perform searchs? - Mailing list pgsql-performance

If you need to compare stuff on a day-by-day basis, I think you'll be
much better off just expanding stuff into a table of:

item_id     int NOT NULL
, day       date NOT NULL
, capacitiy ...
, price_per_day ...
, price_per_week ...
, PRIMARY KEY( item_id, day )

(Note that camel case and databases don't mix well...)

Sure, you're de-normalizing here, but the key is that you're putting the
data into a format where you can easily do things like:

SELECT sum(capacity) FROM ... WHERE day = '2006-12-18';

Trying to do that with arrays would be noticably more complex. And if
you wanted to do a whole month or something? Yeck...

BTW, another option is to roll price_per_15_days and price_per_month
into a different table, since you'd only need 24 rows per item. Might be
worth the trade-off in complexity depending on the specifics of the
application.

On Wed, Mar 08, 2006 at 03:28:36PM +0100, Ruben Rubio Rey wrote:
> Hi,
>
> Im having a dude with a new inplementation in a web site.
> The ojective is create a search as fast as possible. I have thought two
> possibilities to do that:
>
> I have several items. Those items has 1 or more of capacity. Each
> capacity, has several dates (From 1 january to 10 of april, for
> example). The dates covers 366 days, the current year, and they are
> indeterminated ranges. Per each date, it has price per day, per week,
> per15days and per month.
>
> I have designed two possibilities:
>
> First:
> IdItem   StartDate    EndDate    Capacity   PricePerDay   PricePerWeek*
>  PricePer15days*    PricePerMonth*
>    1         1-1-2005     10-1-2005         2                100
>             90                     85                           80
>    1       11-1-2005     20-1-2005         2                105
>             94                     83                           82
>    1       21-1-2005       5-2-2005         4                405
>           394                   283                         182
>    2       ...
> Right now arround 30.000 rows, in one year is spected to have 60.000 rows
>
> * In order to compare right, all prices will be translated to days.
> Example, PricePerWeek will have the Week Price / 7 and go on
>
> Second
> IdItem   Capacity   Days
>                        Week       15Days       Month       Year
>   1              2          [Array of  365 values,  one per day of
> year]     [ .Array. ]   [ .Array. ]   [ .Array. ]   [ .Array. ]
>                               ^__ Each item of array its a price
>
> Right now arround 2.500 rows. in one year is spected to have 5.000 rows
>
> I have to compare prices or prices and dates or prices and dates and
> capacity or capacity and prices
>
> I have no experience working with arrays on a table. Is it fast?
> Witch one do u think will have better performance?
> Any good idea?
>
> I hope this is enouth information.
> Thanks in advance,
> Ruben Rubio Rey
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461