On 8/29/13 9:22 PM, Bastiaan Olij wrote:
Work well enough.. But as I'm using the same data in different reports
and I though a view might be smart. So I created a view:
----
create view v_costs as
select dcjobid as costjobid, sum(dcamount) as costamount
from directcosts
group by dcjobid
union all
select invjobid as costjobid, sum(detamount) as costamount from
invoiceheader
join finvoicedetail on detinvid = invid
group by invjobid
----
And rewrote my report to:
----
select jobid, jobdesc, sum(costamount)
from jobs
join v_costs on costjobid = jobid
where <some filter for my jobs>
group by jobid, jobdesc
----
Now what I was hoping for was that postgres would start at my jobs
table, find the records I'm trying to report on and then index scan on
the related tables and start aggregating the amounts.
What it seems to do is to first execute the view to get totals for all
the jobs in the database and join that result set with my 2 or 3 jobs
that match my filter.
What is it about my view that prevents postgres to effectively use it?
The group bys? the union?
It's probably either the GROUP BY or the UNION. Try stripping those out one at
a time and see if it helps. If it doesn't, please post EXPLAIN ANALYZE (or at
least EXPLAIN) output.
--
Jim C. Nasby, Data Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
