Search Postgresql Archives

Re: When to store data that could be derived

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sun, Mar 24, 2019 at 9:05 AM Frank <frank@xxxxxxxxxxxx> 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.

There are a few other things to note here.

1  If the data is frequently used in its derivative form, putting it in a function helps, and
2.  You can index the output of the function which means you run it on insert/update time and can often avoid running it on selection time if it is just a part of the where clause.

In my experience usually  we have used trigger-updated functions when the field values are very large or expensive, and may need to be part of the column list, and functions with functional indexes when we don't need to ever put them in the select column list or where the columns are small and easy to calculate.

--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.
http://www.efficito.com/learn_more

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux