Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E6FBD9FC2A0 for ; Tue, 30 Oct 2007 10:42:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 63348-01-6 for ; Tue, 30 Oct 2007 10:42:48 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.247]) by postgresql.org (Postfix) with ESMTP id 7854E9FC27D for ; Tue, 30 Oct 2007 10:42:45 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id b33so264491ana for ; Tue, 30 Oct 2007 06:42:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:in-reply-to:references:mime-version:content-type:message-id:cc:content-transfer-encoding:from:subject:date:to:x-mailer; bh=oe2OgmhPt1Ef+bq5dAdg3YF/fblbpHmkb8z0NGaPdpM=; b=oYYNypZQV09k29ku4UCK1HR2hmVgbNOxvCTodMGwVoooRPVLbB9aMyPQoJFx2tvfZJvoBANqN4hTGttsnsTGdYZy8cuDua9Xie0CqUsteygB3H8oySA6C9dHZFDBRI1NRFdVZdOWjK3cGDw1Emm0L8GC+hz2enj4FoGWDQlSzrE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:in-reply-to:references:mime-version:content-type:message-id:cc:content-transfer-encoding:from:subject:date:to:x-mailer; b=FpHor4zK923DRu4AMY415PmhZkrbqWaKf8iNXdBeYKzhULuqgdV9l+IQcSIisLPZipxBCjR8h+7ahJFVY79uCE3n3FDfqBsYXM8J2Zc9YGtfxm8nhRBZ36TEy3a630ITHgBbGsRyeILtBE81DFnOhjQy19wzpxUZPEM9HSFDrso= Received: by 10.100.106.1 with SMTP id e1mr3026376anc.1193751764148; Tue, 30 Oct 2007 06:42:44 -0700 (PDT) Received: from ?192.168.0.20? ( [24.227.61.54]) by mx.google.com with ESMTPS id d24sm9249662and.2007.10.30.06.42.42 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 30 Oct 2007 06:42:42 -0700 (PDT) In-Reply-To: <4727305B.9050307@archonet.com> References: <1193746737.222960.206150@50g2000hsm.googlegroups.com> <4727305B.9050307@archonet.com> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <17A10AA7-73A1-4543-93C3-DAABA352C149@gmail.com> Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: 7bit From: Ketema Harris Subject: Re: Improving Query Date: Tue, 30 Oct 2007 09:42:02 -0400 To: Richard Huxton X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/358 X-Sequence-Number: 27380 On Oct 30, 2007, at 9:23 AM, Richard Huxton wrote: > Ketema wrote: >> I have the following query that is a part of a function: > > Yikes! Difficult to get a clear view of what this query is doing. It seems complicated because you only have a small subset of the schema these tables tie into. Be happy to share the whole thing, if it is needed. > > OK, I'm assuming you're vacuumed and analysed on all these tables... Yes. Auto-vacuum is on and do a Full vacuuum every 2 days. > > >> 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) > > That's taking hardly any time, the startup time is 16576.997 > already. Of course, the row estimate is *way* out of line. OK. I misread the plan and took start up time as the time it took to perform operation. Thanks for the link to explain analyze. > > If you look here (where the explain is a bit easier to see) > http://explain-analyze.info/query_plans/1258-query-plan-224 > > The two main things to look at seem to be the nested loops near the > top and a few lines down the materialise (cost=326... > > These two nested loops seem to be pushing the row estimates wildly > out of reality. They also consume much of the time. > > The immediate thing that leaps out here is that you are trying to > join an int to an array of ints. Why are you using this setup > rather than a separate table? I see what you are talking about. When I initially used this set up it was because I wanted to avoid a table that had a ton of rows in it that I knew I would have to join to often. So I made a column that holds on average 4 or 5 ints representing "products" on a particular "order". I did not realize that using a function in the join would be worse that simply having a large table. > >> How can I improve this step? >> Things I have thought about: >> 1)Creating indexes on the aggregates...Found out this can't be done. > > Nope - not sure what it would mean in any case. My initial thought was the counts were causing the slow up. THis is not the issue though as you have shown. > >> 2)Create Views of the counts and the sub select...is this any faster >> as the view is executed at run time anyway? > > Might make the query easier to write, won't make it faster. Not > without materialised views which are the fancy name for #3... > >> 3)Create actual tables of the sub select and aggregates...How would >> this be maintained to ensure it was always accurate? > > Triggers. Because of the use of this system I may take this route as I think it will be less changes. > >> 4)Increasing hardware resources. Currently box is on a single >> processor amd64 with 8Gb of RAM. below are the settings for resource >> usage. >> shared_buffers = 65536 >> temp_buffers = 5000 >> max_prepared_transactions = 2000 > > ???? These are settings out of postgresql.conf Currently systctl.conf is set to kernel.shmmax = 805306368 connections are at 300 and I usually have about 200 connections open. > >> work_mem = 131072 >> maintenance_work_mem = 512000 > > Can't say about these without knowing whether you've got only one > connection or 100. > >> max_stack_depth = 7168 >> max_fsm_pages = 160000 >> max_fsm_relations = 4000 >> The only function of this box if for Pg, so I do not mind it using >> every last drop of ram and resources that it can. >> 5)Upgrade version of pg..currently is running 8.1.4 > > Well every version gets better at planning, so it can't hurt. At one point I did go to 8.2.3 on a dev box and performance was horrible. Have not had opportunity to see how to make postgresql.conf file in 8.2 match settings in 8.1 as some things have changed. > > -- > Richard Huxton > Archonet Ltd