From: Jim C. Nasby <jnasby@pervasive.com>
To: Ruben Rubio Rey <ruben@rentalia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Is good idea an array of 365 elements in a cell of a table, in order to perform searchs?
Date: Wed, 8 Mar 2006 12:53:10 -0600
Message-ID: <20060308185310.GG45250@pervasive.com> (raw)
In-Reply-To: <440EEA14.70606@rentalia.com>
References: <440EEA14.70606@rentalia.com>
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
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-performance@postgresql.org
Cc: jnasby@pervasive.com, ruben@rentalia.com
Subject: Re: Is good idea an array of 365 elements in a cell of a table, in order to perform searchs?
In-Reply-To: <20060308185310.GG45250@pervasive.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