Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 851F54761DD for ; Thu, 16 Jan 2003 12:28:21 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id CCEF147619E for ; Thu, 16 Jan 2003 12:28:20 -0500 (EST) Received: from [63.195.55.98] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2) with HTTP id 2316193; Thu, 16 Jan 2003 09:28:28 -0800 From: "Josh Berkus" Subject: Re: 7.3.1 New install, large queries are slow To: "Roman Fail" , "Josh Berkus" , "Tom Lane" , Cc: X-Mailer: CommuniGate Pro Web Mailer v.4.0.2 Date: Thu, 16 Jan 2003 09:28:28 -0800 Message-ID: In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C02@pos_pdc.posportal.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/161 X-Sequence-Number: 808 Roman, > > Hey, Roman, how many records in BatchDetail, anyway? > > 23 million. And MSSQL is returning results in 3 seconds? I find that a bit hard to believe, unless this query is called repeatedly and that's the figure for the last call, where the records are being cached. I'll have to look at your hardware descriptions again. > It seems to me that the big, big isolated problem is the index scan > on batchdetail.tranamount. Nope. This was a misimpression caused by batchdetail waiting for a bunch of other processes to complete. Sometimes the parallelizing gives me a wrong impression of what's holding up the query. Sorry if I confused you. > I hope we can come up with something soon.....it seems this index > scan is a big part of the problem. I'm still really curious why the > disk reads are so few with the index scan. Let's hope I can get it > near the 3 second time for MSSQL by Friday! Um, Roman, keep in mind this is a mailing list. I'm sure that everyone here is happy to give you the tools to figure out how to fix things, but only in a DIY fashion, and not on your schedule. If you have a deadline, you'd better hire some paid query/database tuning help. DB Tuning experts .... whether on MSSQL or Postgres ... run about $250/hour last I checked. -Josh Berkus