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 10010310177 for ; Fri, 27 Jun 2003 09:11:11 +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 36728-04 for ; Fri, 27 Jun 2003 06:11:00 -0300 (ADT) Received: from server.beierfeld.kabeljournal.de (pD9529362.dip.t-dialin.net [217.82.147.98]) by svr1.postgresql.org (Postfix) with SMTP id 65F963101D4 for ; Fri, 27 Jun 2003 06:10:59 -0300 (ADT) Received: (qmail 10581 invoked from network); 27 Jun 2003 09:10:58 -0000 Received: from station158.beierfeld.kabeljournal.de (192.168.128.158) by server.beierfeld.kabeljournal.de with SMTP; 27 Jun 2003 09:10:58 -0000 Date: Fri, 27 Jun 2003 11:10:58 +0200 From: Andre Schubert To: Manfred Koizar Cc: tgl@sss.pgh.pa.us, pgsql-performance@postgresql.org Subject: Re: problem with pg_statistics Message-Id: <20030627111058.08f71b14.andre@km3.de> In-Reply-To: <4avnfvkjr8q7cl8ac6f01j8rddj4pjlte0@4ax.com> References: <20030626151515.5fd0faf0.andre@km3.de> <24083.1056636485@sss.pgh.pa.us> <25814.1056643432@sss.pgh.pa.us> <20030627080735.66d6bdf0.andre.schubert@km3.de> <4avnfvkjr8q7cl8ac6f01j8rddj4pjlte0@4ax.com> Organization: km3 teledienst GmbH X-Mailer: Sylpheed version 0.9.1 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200306/186 X-Sequence-Number: 2270 On Fri, 27 Jun 2003 10:43:01 +0200 Manfred Koizar wrote: > On Fri, 27 Jun 2003 08:07:35 +0200, Andre Schubert > wrote: > >Traffic data are inserted every 5 minutes with the actual datetime > >of the transaction, thatswhy the table should be physically order by time_stamp. > > So I'd expect a correlation of nearly 1. Why do your statistics show > a value of -0.479749? A negative correlation is a sign of descending > sort order, and correlation values closer to 0 indicate poor > correspondence between column values and tuple positions. > > Could this be the effect of initial data loading? Are there any > updates or deletions in your traffic table? > We dont make updates the traffic table. Once a month we delete the all data of the oldest month. And after that a vacuum full verbose analyze is performed. Could this cause reordering of the data ? And should i do a cluster idx_ts tbl_traffic ? > >To answer Manfreds questions: > >> Andre, what hardware is this running on? What are the values of > >> shared_buffers, random_page_cost, effective_cache_size, ... ? Could > >> you show us the result of > >> > >> SELECT * FROM pg_stats > >> WHERE tablename = "tbl_traffic" AND attname = "time_stamp"; > ^ ^ ^ ^ > Oops, these should have been single quotes. It's too hot here these > days :-) > You are so right ... :) > >#effective_cache_size = 1000 # default in 8k pages > > This is definitely too low. With 512MB or more I tend to set this to > ca. 80% of available RAM. Use top and free to find hints for good > values. > Ok, i will talk with my coworker ( he is the sysadmin of our machine ) and look if can use such amount of RAM, because there are several other processes that are running on these machines. But i will test and report ... Thanks, as