From: Tom Lane <tgl@sss.pgh.pa.us>
To: John McCawley <nospam@hardgeus.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance of a view
Date: Tue, 20 Dec 2005 18:31:15 -0500
Message-ID: <7209.1135121475@sss.pgh.pa.us> (raw)
In-Reply-To: <437B6974.5040803@hardgeus.com>
References: <437B6974.5040803@hardgeus.com>
John McCawley <nospam@hardgeus.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, nospam@hardgeus.com
Subject: Re: Performance of a view
In-Reply-To: <7209.1135121475@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox