On Oct 30, 2007, at 9:31 AM, Michael Glaesemann wrote:
> 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
>> (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
Yes. I run vacuum FULL ANALYZE VERBOSE every two days with a cron job.
I am running again now any way.
> Do you have enable_seqscan disabled? It appears so, due to the high
> cost here:
> -> 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?
it was disabled. new plan posted here:
>> 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
> server configuration.
> 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 looking for.
> Michael Glaesemann
> grzm seespotcode net
In response to
pgsql-performance by date
|Next:||From: Marc Schablewski||Date: 2007-10-30 14:21:03|
|Subject: Re: Optimizing PostgreSQL for Windows|
|Previous:||From: Ketema Harris||Date: 2007-10-30 13:42:02|
|Subject: Re: Improving Query|