Re: Store derived data or use view?

From: Michael Black <michaelblack75052(at)hotmail(dot)com>
To: <mgainty(at)hotmail(dot)com>, <gvimrc(at)googlemail(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Store derived data or use view?
Date: 2009-07-03 22:23:55
Message-ID: BLU144-W14D3D3DF32A015406127A8FA2C0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


In addition to the amount of data that will be captured, you will need
take in to consideration how often each user will be accessing this
data as well as the number of users. For example, if you have 10 users
running the query once an hour every hour of the day, you might get
away with calculating the aspect each time the data data is requested.
But if you have 100 users wanting the data every 15 minutes, you may
find it faster to perform the calculations on the insert of the
planetary data. Personally, and since you indicated that there will a
large amount of data, I would do the calculations on insert.

I am not associated with NASA.

From: mgainty(at)hotmail(dot)com
To: gvimrc(at)googlemail(dot)com; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Store derived data or use view?
Date: Fri, 3 Jul 2009 15:46:50 -0400

i just asked NASA the same question
I'll post the answer back to the list..

Martin Gainty
We can lick gravity, but sometimes the paperwork is overwhelming.
- Wehrner Von Braun
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Fri, 3 Jul 2009 18:08:17 +0100
> From: gvimrc(at)googlemail(dot)com
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Store derived data or use view?
>
> I have a table which stores the absolute longitude of a planetary position, eg:
>
> MERCURY
> ---------------
> 157.65
>
> SATURN
> -------------
> 247.65
>
> When 2 planets are a certain distance apart there is an 'aspect', eg. 90 degrees is a "square" aspect
>
> I wish to record these aspects for different user profiles and eventually do searches for users who have the same aspect(s). Would it be better, in terms of search speed/efficiency, to calculate and store the aspect data, eg. Mercury/Saturn square, or should I just store the longitude data and create a view with the calculated aspects? I anticipate a large dataset of users so search speed/efficiency is very important.
>
> gvim
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Insert movie times and more without leaving Hotmail®. See how.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Scott Bailey 2009-07-03 23:57:35 Re: Installing plpython on 8.4
Previous Message ANdreas Wenk 2009-07-03 20:05:20 Re: PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors