Re: [GENERAL] a JOIN to a VIEW seems slow

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?

 

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

 

Thanks for any pointers.

 

Frank Millman