Re: a JOIN to a VIEW seems slow

2017-09-14 10:14 GMT+02:00 Frank Millman <frank(at)chagford(dot)com>:

> Hi all
>
> This is a follow-up to a recent question I posted regarding a slow query.
> I thought that the slowness was caused by the number of JOINs in the query,
> but with your assistance I have found the true reason. I said in the
> previous thread that the question had become academic, but now that I
> understand things better, it is no longer academic as it casts doubt on my
> whole approach.
>
> I have split my AR transaction table into three physical tables –
> ar_tran_inv, ar_tran_crn, ar_tran_rec. I will probably add others at some
> point, such as ar_tran_jnl.
>
> I then create a VIEW to view all transactions combined. The view is
> created like this -
>
> CREATE VIEW ar_trans AS
> SELECT ‘ar_inv’ AS tran_type, row_id AS tran_row_id, tran_number ...
> FROM ar_tran_inv WHERE posted = ‘1’
> UNION ALL
> SELECT ‘ar_crn’ AS tran_type, row_id AS tran_row_id, tran_number ...
> FROM ar_tran_crn WHERE posted = ‘1’
> UNION ALL
> SELECT ‘ar_rec’ AS tran_type, row_id AS tran_row_id, tran_number ...
> FROM ar_tran_rec WHERE posted = ‘1’
>
> I have another table called ‘ar_trans_due’, to keep track of outstanding
> transactions. All of the three transaction types generate entries into this
> table. To identify the source of the transaction, I have created columns in
> ar_trans_due called ‘tran_type’ and ‘tran_row_id’. After inserting a row
> into ‘ar_tran_inv’, I invoke this -
>
> INSERT INTO ar_trans_due (tran_type, tran_row_id, ...) VALUES (‘ar_inv’,
> ar_tran_inv.row_id, ...), and similar for the other transaction types. It
> is handled by a Python program, and it all happens within a transaction.
>
> When I view a row in ar_trans_due, I want to retrieve data from the source
> transaction, so I have this -
>
> SELECT * FROM ar_trans_due a
> LEFT JOIN ar_trans b ON b.tran_type = a.tran_type AND b.tran_row_id =
> a.tran_row_id
>
> I understand that PostgreSQL must somehow follow a path from the view
> ‘ar_trans’ to the physical table ‘ar_tran_inv’, but I assumed it would
> execute the equivalent of SELECT * FROM ar_tran_inv WHERE row_id =
> a.tran_row_id AND posted = ‘1’.
>
> If this was the case, it would be an indexed read, and very fast. Instead,
> according to EXPLAIN, it performs a sequential scan of the ‘ar_tran_inv’
> table.
>
> It also scans ‘ar_tran_crn’ and ‘ar_tran_rec’, but EXPLAIN shows that it
> uses a Bitmap Heap Scan on those. I assume that is because the tables are
> currently empty.
>
> Is this analysis correct?
>

please, send EXPLAIN ANALYZE result :)

>
> If so, is there any way to force it to use an indexed read?
>

set enable_seqscan to off;

Regards

Pavel

>
> Thanks for any pointers.
>
> Frank Millman
>
>