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)
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
