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 C487230EF13 for ; Fri, 27 Jun 2003 06:13:22 +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 09774-01 for ; Fri, 27 Jun 2003 03:13:12 -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 302A730E656 for ; Fri, 27 Jun 2003 03:13:11 -0300 (ADT) Received: (qmail 5873 invoked from network); 27 Jun 2003 06:13:08 -0000 Received: from station158.beierfeld.kabeljournal.de (192.168.128.158) by server.beierfeld.kabeljournal.de with SMTP; 27 Jun 2003 06:13:08 -0000 Date: Fri, 27 Jun 2003 08:13:06 +0200 From: Andre Schubert To: Tom Lane Cc: mkoi-pg@aon.at, pgsql-performance@postgresql.org Subject: Re: problem with pg_statistics Message-Id: <20030627081306.71ffbb2e.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/184 X-Sequence-Number: 2268 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? > Sorry forgot the pg_stat query... SELECT * FROM pg_stats where tablename = 'tbl_traffic' and attname = 'time_stamp'; tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation -------------+------------+-----------+-----------+------------+------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------+--------------------------------------------------------------------+--------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------+------------- tbl_traffic | time_stamp | 0 | 8 | 104009 | {"2003-06-03 19:12:01.059625+02","2003-02-03 19:52:06.666296+01","2003-02-13 09:59:45.415763+01","2003 -02-28 18:10:28.536399+01","2003-04-11 18:09:42.30363+02","2003-04-26 20:35:50.110235+02","2003-05-03 11:09:32.991507+02","2003-05-20 09:53:51.271853+02","2003-05-21 2 0:55:59.155387+02","2003-06-02 02:38:28.823182+02"} | {0.00133333,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001} | {"2002-07-01 00:00:00+02","2003-02-21 01:59: 46.107696+01","2003-03-11 15:00:37.418521+01","2003-03-26 18:14:50.028972+01","2003-04-10 13:43:20.75909+02","2003-04-27 09:03:19.592213+02","2003-05-08 22:35:41.99761 6+02","2003-05-22 15:34:42.932958+02","2003-06-03 00:53:05.870782+02","2003-06-15 08:45:41.154875+02","2003-06-27 07:18:30.265868+02"} | -0.479749 (1 row) Thanks, as