Re: Performance of a view

On Nov 14, 2005, at 7:40 PM, John McCawley wrote:

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

<snip>

> I roughly understand what is happening...in the first query, the
> dataset is being knocked down to one row, then somehow the view is
> being constructed using only that subset of the claim table.  In
> the second query, the view is being constructed from the entire
> dataset which is hundreds of thousands of rows, and thus is much
> slower.
>
> My question is how would I go about obtaining the behavior from the
> faster query in the slower query?  I have switched the order of the
> tables, and tried many different permutations of the query, but no
> matter what I do, it seems that unless I specifically hard-code a
> claim_id filter on the claim_id, I am forced to run through every
> record.
>
> Thoughts?

I'd be curious to see what would happen if you added claimnum as a
field in your view. I don't have a complete understanding of the
postgres internals in terms of how it is able to push outer clauses
down in to its views, but I think it might be able to optimize in
that fashion if it is able to add a WHERE clause internally to the
view, which it can't do in the case of claimnum since it doesn't
exist in the view.

--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)