|
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
|
