From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: When to store data that could be derived |
Date: | 2019-03-24 08:45:27 |
Message-ID: | 6d9e9fc2-cfd9-c36f-b813-0f2f4492da8f@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/24/19 3:05 AM, Frank wrote:
>
>
> On 2019-03-24 9:25 AM, Ron wrote:
>> On 3/24/19 1:42 AM, Frank wrote:
>>> Hi all
>>>
>>> As I understand it, a general rule of thumb is that you should never
>>> create a physical column if the data could be derived from existing
>>> columns. A possible reason for breaking this rule is for performance
>>> reasons.
>>>
>>> I have a situation where I am considering breaking the rule, but I am
>>> not experienced enough in SQL to know if my reason is valid. I would
>>> appreciate it if someone could glance at my 'before' and 'after'
>>> scenarios and see if, from a 'gut-feel' point of view, I should proceed.
>>>
>
> [snip]
>
>>
>> Sure the second query joins a lot of tables, but is pretty straightforward.
>>
>> What REALLY worries me is whether or not the query optimiser would look
>> at the WHERE CASE, run away screaming and then make it use sequential
>> scans. Thus, even query #1 would be slow.
>>
>
> I had not realised that. I hope someone else chimes in on this.
In every DBMS that I've used, the lside (left side) needs to be static (not
"a" static) instead of variable (like a function).
For example, this always leads to a sequential scan:
WHERE EXTRACT(DAY FROM DATE_FIELD) = 5
>
>>
>> Is this a historical data set that's never updated, or current data
>> that's constantly added to?
>>
>
> It is the latter - current data constantly added to.
>
> Frank
>
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Dean Rasheed | 2019-03-24 08:54:52 | Re: regr_slope returning NULL |
Previous Message | Frank | 2019-03-24 08:05:02 | Re: When to store data that could be derived |