pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimising views
2+ messages / 2 participants
[nested] [flat]
* Optimising views
@ 2013-08-30 02:22 Bastiaan Olij <bastiaan@basenlily.me>
2013-09-12 15:10 ` Re: Optimising views Jim Nasby <jim@nasby.net>
0 siblings, 1 reply; 2+ messages in thread
From: Bastiaan Olij @ 2013-08-30 02:22 UTC (permalink / raw)
To: pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Optimising views
2013-08-30 02:22 Optimising views Bastiaan Olij <bastiaan@basenlily.me>
@ 2013-09-12 15:10 ` Jim Nasby <jim@nasby.net>
0 siblings, 0 replies; 2+ messages in thread
From: Jim Nasby @ 2013-09-12 15:10 UTC (permalink / raw)
To: Bastiaan Olij <bastiaan@basenlily.me>; +Cc: pgsql-performance
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 jim@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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-09-12 15:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-30 02:22 Optimising views Bastiaan Olij <bastiaan@basenlily.me>
2013-09-12 15:10 ` Jim Nasby <jim@nasby.net>
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