Hi all, I've started using views in an attempt to try and simplify some of my more complex reporting and running into some snags and I'm trying to figure out how Postgres decides to join data with my view. Just to present a simplified representation of what I am trying to accomplish the following would match pretty well with what I'm doing. At the center I have a table called "jobs" that holds information about jobs clients have requested. Related to my jobs are a number of different tables that each hold different types of costs. There is a simple table called "directcosts" that simply holds single items that represent a cost on that job There is a more complex invoice/detail structure for invoices received by suppliers who did things in relation to the job And a few more structures like that. What I currently do when I need to report on the total costs per job, and which works very well, is the following: ---- select jobid, jobdesc, sum(cost) from ( select jobid, jobdesc, dcamount as cost from jobs join directcosts on jobid = dcjobid where <some filter for my jobs> union all select jobid, jobdesc, detamount as cost from jobs join invoiceheader on jobid = invjobid join invoicedetail on detinvid = invid where <some filter for my jobs> ) totalcosts group by jobid, jobdesc ---- 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? Cheers, Bastiaan Olij -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
