X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C1FCAD1D090 for ; Wed, 7 Apr 2004 20:48:39 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 70073-01 for ; Wed, 7 Apr 2004 20:48:40 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id AC568D1D088 for ; Wed, 7 Apr 2004 20:48:36 -0300 (ADT) Received: from tmsl-adsl.demon.co.uk ([80.177.114.181] helo=bacon.tmsl.demon.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1BBMmd-000FuO-0V for pgsql-performance@postgresql.org; Thu, 08 Apr 2004 00:48:39 +0100 Date: Thu, 8 Apr 2004 00:48:35 +0100 From: Paul Thomas To: "pgsql-performance @ postgresql . org" Subject: Re: statistics Message-ID: <20040408004835.A8476@bacon> References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 In-Reply-To: ; from el_vigia_ec@hotmail.com on Wed, Apr 07, 2004 at 22:05:15 +0100 X-Mailer: Balsa 1.2.3 Lines: 60 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/93 X-Sequence-Number: 6393 On 07/04/2004 22:05 Jaime Casanova wrote: > What the statistics are? Where can i view it? where can i find info > about its field and why are they valuable information to performance? > > thanx in advance, > > Jaime Casanova OK. An idiot's guide to statistics by a full-time idiot... Let's start with a simple premise. I'm a RDBMS (forget that I'm actually an idiot for a moment...) and I've been asked for select * from foo where bar = 7; How do I go about fulfilling the reequest in the most efficient manner? (i.e., ASAP!) One way might be to read through the whole table and return only those rows which match the where criteron - a sequential scan on the table. But wait a minute, there is an index on column bar. Could I use this instead? Well, of course, I could use it but I have to keep sight of the goal of returning the data ASAP and I know that the act of reading index/reading table/... will have a performance penalty due to a lot more head movement on the disk. So how do I make chose between a sequential scan and an index scan? Let's lokk at a couple of extreme scenarios: 1) let's look at the condition where all or virtually all of the bar columns are populated wityh the value 7. In this case it would be more efficient to read sequentially through the table. 2) the opposite of (1) - very few of the bar columns have the value 7. In this case using the index could be a winner. So generalising, I need to be able to estimate whether doing a sequential scan is more efficient that an index scan and this comes down to 2 factors: a) the cost of moving the disk heads all over the place (random page cost) b) the spread of values in the selecting column(s) (a) is specfified in postgresql.conf (see archives for much discusion about what the value should be..) (b) is determined by the dastardly trick of actually sampling the data in the table!!! That's what analyze does. It samples your table(s) and uses the result to feeede into it's descision about when to flip between sequential and index scans. Hope this makes some kind of sense... -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+