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 274B29F9FE3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 22 Jun 2006 08:29:56 -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 61960-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 22 Jun 2006 08:29:45 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.aeccom.com (port-83-236-156-26.static.qsc.de
	[83.236.156.26])
	by postgresql.org (Postfix) with ESMTP id C17999F9ED6
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Jun 2006 08:29:46 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.aeccom.com (Postfix) with ESMTP id 6431E1C72C;
	Thu, 22 Jun 2006 13:29:44 +0200 (CEST)
Received: from mail.aeccom.com ([127.0.0.1])
	by localhost (gate6.aeccom.com [127.0.0.1]) (amavisd-new, port 10024)
	with LMTP id 29464-03-8; Thu, 22 Jun 2006 13:29:43 +0200 (CEST)
Received: from [192.168.2.140] (unknown [192.168.2.140])
	by mail.aeccom.com (Postfix) with ESMTP id 818CF1C71E;
	Thu, 22 Jun 2006 13:29:43 +0200 (CEST)
Message-ID: <449A7F25.3040607@aeccom.com>
Date: Thu, 22 Jun 2006 13:29:41 +0200
From: Sven Geisler <sgeisler@aeccom.com>
Organization: AEC/communications GmbH
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: nicky <nicky@valuecare.nl>
CC: pgsql-performance@postgresql.org
Subject: Re: Speeding up query, Joining 55mil and 43mil records.
References: <44994DE7.5070801@valuecare.nl>
	<20060621184803.GC93655@pervasive.com>
	<449A677D.4020504@valuecare.nl>
In-Reply-To: <449A677D.4020504@valuecare.nl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: amavisd-new at aeccom.com
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/440
X-Sequence-Number: 19797

Hi Nicky,

Did you tried to create an index to avoid the sequential scans?

Seq Scan on src_faktuur_verrsec t0...

I think, you should try

CREATE INDEX src.src_faktuur_verrsec_codesubstr ON 
src.src_faktuur_verrsec (substr(src.src_faktuur_verrsec.code,1,2))

Cheers
Sven.

nicky schrieb:
> Hello again,
> 
> thanks for all the quick replies.
> 
> It seems i wasn't entirely correct on my previous post, i've mixed up 
> some times/numbers.
> 
> Below the correct numbers
> 
> MSSQL:      SELECT COUNT(*) from JOIN (without insert)   17 minutes
> PostgreSQL: SELECT COUNT(*) from JOIN (without insert)   33 minutes
> PostgreSQL: complete query                               55 minutes

  <snip snip snip>
> 
> A lot of improvement also in the select count: 33 minutes vs 10 minutes.
> 
> 
> To us, the speeds are good. Very happy with the performance increase on 
> that select with join, since 90% of the queries are SELECT based.
> 
> The query results in 7551616 records, so that's about 4500 inserts per 
> second. I'm not sure if that is fast or not. Any further tips would be 
> welcome.