Re: When to store data that could be derived

On 2019-03-25 5:11 PM, Frank wrote:
> 
> 
> On 2019-03-25 4:06 PM, Ron wrote:
>> On 3/25/19 8:15 AM, Frank wrote:
>>
>> It would be interesting to see what the query planner tries to do with 
>> this:
>>
>> WHERE
>>      CASE
>>          WHEN a.tran_type = 'ar_rec' THEN y.posted
>>          WHEN a.tran_type = 'cb_rec' THEN w.posted
>>      END = '1'
>>
> 
> I have attached the schema showing the full VIEW definition, and the 
> result of the following EXPLAIN -
> 
> EXPLAIN SELECT * FROM ccc.ar_trans WHERE cust_row_id = 4 AND tran_date 
> BETWEEN '2015-06-01' AND '2015-06-30'.
> 
> Because I have used 'WHERE tran_date' in the query, and tran_date is 
> also derived from a CASE expression, I imagine that will also add some 
> complication.
> 
> I am running PostgreSQL 11.1 on Fedora 29.
> 
> Frank

On reflection, I have not been consistent with my use of indexes, and I 
think that will affect the query plan.

There are at least two issues -

1. The first table defined in the VIEW is ccc.ar_tran_inv. It has the 
following index -
     "arinv_cust_date" btree (cust_row_id NULLS FIRST, tran_date NULLS 
FIRST) WHERE deleted_id = 0

I have not used "WHERE deleted_id = 0" when constructing the VIEW, but I 
have used "WHERE posted = '1'". I don't think the index can be used with 
this setup.

2. The complicated table in the VIEW is ccc.ar_rec_subtran. Various 
columns such as tran_date and posted are retrieved via CASE expressions 
from two underlying tables. Those tables have certain indexes defined, 
but I cannot see how they can be utilised from my current setup.

I think I should spend some time tidying this up before you try to make 
sense of the query plan. Any tips on how to improve it will be appreciated.

Frank