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