Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 309229F9D62 for ; Tue, 30 Oct 2007 10:32:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 89631-01 for ; Tue, 30 Oct 2007 10:32:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mtai05.charter.net (mtai05.charter.net [209.225.8.185]) by postgresql.org (Postfix) with ESMTP id 1CF999F9265 for ; Tue, 30 Oct 2007 10:32:15 -0300 (ADT) Received: from aarprv04.charter.net ([10.20.200.74]) by mtai05.charter.net (InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP id <20071030133204.LZJD7894.mtai05.charter.net@aarprv04.charter.net>; Tue, 30 Oct 2007 09:32:04 -0400 Received: from [10.0.1.4] (really [66.188.156.151]) by aarprv04.charter.net with ESMTP id <20071030133202.THOR23773.aarprv04.charter.net@[10.0.1.4]>; Tue, 30 Oct 2007 09:32:02 -0400 In-Reply-To: <1193746737.222960.206150@50g2000hsm.googlegroups.com> References: <1193746737.222960.206150@50g2000hsm.googlegroups.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: Improving Query Date: Tue, 30 Oct 2007 08:31:46 -0500 To: Ketema X-Mailer: Apple Mail (2.752.2) X-Chzlrs: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/357 X-Sequence-Number: 27379 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