Skip site navigation (1) Skip section navigation (2)

Re: Improving Query

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Ketema <ketema(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Improving Query
Date: 2007-10-30 13:31:46
Message-ID: BB8E6D54-3814-4159-B173-9A541333483E@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-performance
On Oct 30, 2007, at 7:18 , Ketema wrote:

> here is the execution plan:

I've put this online here:

http://explain-analyze.info/query_plans/1259-ketema-2007-10-30

> 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/ 
> provision_list_tables_erd.jpg
>
> 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  
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)

http://explain-analyze.info/query_plans/1259-ketema-2007-10-30#node-3594

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  
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

Responses

pgsql-performance by date

Next:From: Ketema HarrisDate: 2007-10-30 13:42:02
Subject: Re: Improving Query
Previous:From: Christian RengstlDate: 2007-10-30 13:28:27
Subject: Optimizing PostgreSQL for Windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group