Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6C11747619E for ; Thu, 16 Jan 2003 12:46:57 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id EE9CA47610B for ; Thu, 16 Jan 2003 12:46:56 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 614AAD606; Thu, 16 Jan 2003 09:47:02 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 5704B5C02; Thu, 16 Jan 2003 09:47:02 -0800 (PST) Date: Thu, 16 Jan 2003 09:47:02 -0800 (PST) From: Stephan Szabo To: Josh Berkus Cc: Roman Fail , Tom Lane , Subject: Re: 7.3.1 New install, large queries are slow In-Reply-To: Message-ID: <20030116094210.L6318-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/163 X-Sequence-Number: 810 On Thu, 16 Jan 2003, Josh Berkus wrote: > 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'm still not sure that it isn't a big part given that the time went down by a factor of about 4 when index scans were disabled and a sequence scan was done and that a sequence scan over the table with no other tables joined looked to take about 5 minutes itself and the difference between that seqscan and the big query was only about 20 seconds when enable_indexscan was off unless I'm misreading those results.