Re: Performance of a view

John McCawley <nospam(at)hardgeus(dot)com> writes:
> I have a view which is defined as follows:

> SELECT tbl_claim.claim_id, count(tbl_invoice.invoice_id) AS count,
> min(tbl_invoice.invoicedate) AS invoicedate
> FROM tbl_claim
> LEFT JOIN tbl_invoice ON tbl_claim.claim_id = tbl_invoice.claim_id AND
> tbl_invoice.active = 1
> GROUP BY tbl_claim.claim_id;

> If I run:

> EXPLAIN ANALYZE SELECT
> tbl_claim.claim_id FROM tbl_claim INNER JOIN vw_claiminvoicecount ON
> tbl_claim.claim_id = vw_claiminvoicecount.claim_id WHERE
> tbl_claim.claim_id = 217778;

> [ it's fast ]

> However, if I run:

> EXPLAIN ANALYZE SELECT
> tbl_claim.claim_id FROM tbl_claim INNER JOIN vw_claiminvoicecount ON
> tbl_claim.claim_id = vw_claiminvoicecount.claim_id WHERE
> tbl_claim.claimnum = 'L1J8823';

> [ it's not ]

I finally got around to looking at this. The reason the first case is
fast is that the planner is able to deduce the extra condition
vw_claiminvoicecount.claim_id = 217778, and then push that down into the
view, so that the LEFT JOIN only need be performed for the single
tbl_claim row with that claim_id. In the second case this is not
possible --- the restriction on claimnum doesn't have any connection to
the view that the planner can see. My advice is to extend the view
to show claimnum as well, and then you can forget about the extra join
of tbl_claim and just do
SELECT * FROM vw_claiminvoicecount WHERE claimnum = 'L1J8823';

regards, tom lane