X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 30561D22B8 for ; Fri, 11 Jul 2003 05:54:41 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 15789-01 for ; Fri, 11 Jul 2003 02:54:28 -0300 (ADT) Received: from freebsd47.hub.org (u134n133.eastlink.ca [24.224.134.133]) by svr1.postgresql.org (Postfix) with ESMTP id 9669CCD797 for ; Thu, 10 Jul 2003 12:08:56 -0300 (ADT) Received: from [192.168.0.5] (freebsd47 [192.168.0.5]) by freebsd47.hub.org (Postfix) with ESMTP id 48959A955; Thu, 10 Jul 2003 12:09:10 -0300 (ADT) Subject: Re: Some very weird behaviour.... From: Chris Bowlby Reply-To: excalibur@hub.org To: Rod Taylor Cc: Postgresql Performance In-Reply-To: <1057772554.46100.103.camel@jester> References: <1057771777.33712.98.camel@freebsd47> <1057772554.46100.103.camel@jester> Content-Type: text/plain Organization: Hub.Org Networking Services Message-Id: <1057849750.38433.12.camel@freebsd47> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 10 Jul 2003 12:09:10 -0300 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/170 X-Sequence-Number: 2463 On Wed, 2003-07-09 at 14:42, Rod Taylor wrote: Clustering definatly helped with that case, and appears to have helped with all of the dates I have had high execution times for... thanks for the tip.. > > To give you some perspective on the size of the dataset and the > > performance level we are hitting, here are some "good" results based on > > some explains: > > Before Tom jumps in taking all the fun out of trying to solve it... > > > The estimates in the slow queries seem perfectly reasonable. In fact, > the cost estimates of both the slow and fast queries are the same which > is what would be expected if all of the data was distributed evenly > amongst the table. > > Given it's a date, I would guess that the data is generally inserted > into the table in an order following the date but for some reason those > 'high' dates have their data distributed more evenly amongst the table. > Clustered data will have fewer disk seeks and deal with fewer pages of > information in general which makes for a much faster query. Distributed > data will have to pull out significantly more information from the disk, > throwing most of it away. > > I would guess that sometime on 2002-05-25 someone did a bit of data > cleaning (deleting records). Next day the free space map had entries > available in various locations within the table, and used them rather > than appending to the end. With 89 Million records with date being > significant, I'm guessing there aren't very many modifications or > deletes on it. > > So.. How to solve the problem? If this is the type of query that occurs > most often, you do primarily inserts, and the inserts are generally > created following date, cluster the table by index "some_table_ix_0". > The clustering won't degrade very much since that is how you naturally > insert the data. -- Chris Bowlby Hub.Org Networking Services