Re: When to store data that could be derived

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