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 028B99F9FE3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 22 Jun 2006 09:11:06 -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 66503-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 22 Jun 2006 09:10:54 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp-vbr3.xs4all.nl (smtp-vbr3.xs4all.nl [194.109.24.23])
	by postgresql.org (Postfix) with ESMTP id 93D449F9ED6
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Jun 2006 09:10:55 -0300 (ADT)
Received: from [10.0.0.12] (a80-126-182-198.adsl.xs4all.nl [80.126.182.198])
	(authenticated bits=0)
	by smtp-vbr3.xs4all.nl (8.13.6/8.13.6) with ESMTP id k5MCAnY2091972;
	Thu, 22 Jun 2006 14:10:49 +0200 (CEST)
	(envelope-from nicky@valuecare.nl)
Message-ID: <449A88CA.3000108@valuecare.nl>
Date: Thu, 22 Jun 2006 14:10:50 +0200
From: nicky <nicky@valuecare.nl>
User-Agent: Thunderbird 1.5.0.4 (X11/20060621)
MIME-Version: 1.0
To: Sven Geisler <sgeisler@aeccom.com>
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> <449A7F25.3040607@aeccom.com>
In-Reply-To: <449A7F25.3040607@aeccom.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by XS4ALL Virus Scanner
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/441
X-Sequence-Number: 19798

Hello Sven,

We have the following indexes on src_faktuur_verrsec
/
    CREATE INDEX src_faktuur_verrsec_idx0
      ON src.src_faktuur_verrsec
      USING btree
      (id);

    CREATE INDEX src_faktuur_verrsec_idx1
      ON src.src_faktuur_verrsec
      USING btree
      (substr(code::text, 1, 2));

    CREATE INDEX src_faktuur_verrsec_idx2
      ON src.src_faktuur_verrsec
      USING btree
      (substr(correctie::text, 4, 1));/

and another two on src_faktuur_verricht

/    CREATE INDEX src_faktuur_verricht_idx0
      ON src.src_faktuur_verricht
      USING btree
      (id);

    CREATE INDEX src_faktuur_verricht_idx1
      ON src.src_faktuur_verricht
      USING btree
      (date_part('year'::text, datum))
      TABLESPACE src_index;/

PostgreSQL elects not to use them. I assume, because it most likely 
needs to traverse the entire table anyway.

if i change: /              substr(t0.code,1,2) not in 
('14','15','16','17')/
to (removing the NOT): /    substr(t0.code,1,2) in ('14','15','16','17')/

it uses the index, but it's not the query that needs to be run anymore.

Greetings,
Nick




Sven Geisler wrote:
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>