On Oct 30, 2007, at 7:18 , Ketema wrote:
> here is the execution plan:
I've put this online here:
> I have attached an erd of the tables used in this query. If it is
> stripped out it can be viewed here: http://www.ketema.net/
> My concern is with the sort step that takes 15 seconds by itself:
> -> Sort (cost=1235567017.53..1238002161.29 rows=974057502 width=290)
> (actual time=16576.997..16577.513 rows=3366 loops=1)
What jumps out at me is the huge difference in estimated and returned
rows, and the huge cost estimates. Have you analyzed recently?
Do you have enable_seqscan disabled? It appears so, due to the high
-> Seq Scan on order_details (cost=100000000.0..100000012.45 rows=35
width=199) (actual time=0.001..0.001 rows=0 loops=1)
What does it look like with seqscan enabled?
> 2)Create Views of the counts and the sub select...is this any faster
> as the view is executed at run time anyway?
Views aren't materialized: it's like inlining the definition of the
view itself in the query.
> 3)Create actual tables of the sub select and aggregates...How would
> this be maintained to ensure it was always accurate?
One way would be to update the summaries using triggers. Hopefully
you won't need to do this after analyzing and perhaps tweaking your
Unfortunately I don't have the time to look at the query plan in more
detail, but I suspect there's a better way to get the results you're
grzm seespotcode net
In response to
pgsql-performance by date
|Next:||From: Ketema Harris||Date: 2007-10-30 13:42:02|
|Subject: Re: Improving Query|
|Previous:||From: Christian Rengstl||Date: 2007-10-30 13:28:27|
|Subject: Optimizing PostgreSQL for Windows|