X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 812329FB203 for ; Thu, 27 Jul 2006 11:47:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 05400-07 for ; Thu, 27 Jul 2006 11:46:58 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id AF0459FB202 for ; Thu, 27 Jul 2006 11:46:28 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Thu, 27 Jul 2006 14:46:26 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 27 Jul 2006 09:46:26 -0500 Subject: Re: performance issue with a specific query From: Scott Marlowe To: Eliott Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1154011586.31664.85.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Thu, 27 Jul 2006 09:46:26 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL, SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200607/242 X-Sequence-Number: 20102 On Thu, 2006-07-27 at 09:23, Eliott wrote: > Hi! > > I hope I'm sending my question to the right list, please don't flame > if it's the wrong one. > > I have noticed that while a query runs in about 1.5seconds on a 8.xx > version postgresql server on our 7.4.13 it takes around 15-20 minutes. > Since we are using RHEL4 on our server we are stuck with 7.4.13. The > enormous time difference between the different builds drives me crazy. > Can you please help me identifying the bottleneck or suggest anything > to improve the dismal performance. You are absolutely on the right list. A couple of points. 1: Which 8.xx? 8.0.x or 8.1.x? 8.1.x is literally light years ahead of 7.4 in terms of performance. 8.0 is somewhere between them. The performance difference you're seeing is pretty common. 2: Looking at your query, there are places where you're joining on things like date_trunc(...). In 7.4 the database will not, and cannot use a normal index on the date field for those kinds of things. It can, however, use a funtional index on some of them. Try creating an index on date_trunc('day',yourfieldhere) and see if that helps. 3: You are NOT Stuck on 7.4.13. I have a RHEL server that will be running 8.1.4 or so pretty soon as a dataware house. It may get updated to RHEL4, may not. You can either compile from the .tar.[gz|bz2] files or download the PGDG rpms for your distro. 4: You are fighting an uphill battle. There were a LOT of improvements made all over in the march from 7.4 to 8.1. Not all of them were simple planner tweaks and shortcuts, but honest to goodness changes to the way things happen. No amount of tuning can make 7.4 run as fast as 8.1.