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 B6B61310116 for ; Fri, 27 Jun 2003 06:07:49 +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 98788-07 for ; Fri, 27 Jun 2003 03:07:39 -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 5FF7230FF56 for ; Fri, 27 Jun 2003 03:07:38 -0300 (ADT) Received: (qmail 5727 invoked from network); 27 Jun 2003 06:07:36 -0000 Received: from station158.beierfeld.kabeljournal.de (192.168.128.158) by server.beierfeld.kabeljournal.de with SMTP; 27 Jun 2003 06:07:36 -0000 Date: Fri, 27 Jun 2003 08:07:35 +0200 From: Andre Schubert To: Tom Lane Cc: mkoi-pg@aon.at, pgsql-performance@postgresql.org Subject: Re: problem with pg_statistics Message-Id: <20030627080735.66d6bdf0.andre.schubert@km3.de> In-Reply-To: <25814.1056643432@sss.pgh.pa.us> References: <20030626151515.5fd0faf0.andre@km3.de> <24083.1056636485@sss.pgh.pa.us> <25814.1056643432@sss.pgh.pa.us> 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/183 X-Sequence-Number: 2267 On Thu, 26 Jun 2003 12:03:52 -0400 Tom Lane wrote: > Manfred Koizar writes: > > On Thu, 26 Jun 2003 10:08:05 -0400, Tom Lane > > wrote: > >> Try reducing random_page_cost > > > With index scan cost being more than 25 * seq scan cost, I guess that > > - all other things held equal - even random_page_cost = 1 wouldn't > > help. > > Oh, you're right, I was comparing the wrong estimated costs. Yeah, > changing random_page_cost won't fix it. > > > Or there's something wrong with correlation? > > That seems like a good bet. Andre, is this table likely to be > physically ordered by time_stamp, or nearly so? If so, do you > expect that condition to persist, or is it just an artifact of > a test setup? > First of all thanks for the quick response. We have three servers at different places, all servers are running with athlon processors and have ram between 512M up to 1024M, and a frequency between 700 and 1400Mhz. All servers running under Linux 7.2 Kernel 2.4.20. We use this table to collect traffic of our clients. Traffic data are inserted every 5 minutes with the actual datetime of the transaction, thatswhy the table should be physically order by time_stamp. All servers are running in production and i could reproduce the problem on all three servers. 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"; The only changes we have made are sort_mem = 32000 shared_buffers = 13000 All other values are commented out and should be set to default by postgres itself. #max_fsm_relations = 100 # min 10, fsm is free space map #max_fsm_pages = 10000 # min 1000, fsm is free space map #effective_cache_size = 1000 # default in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 Hope this help ... Thanks, as