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 765469FA606 for ; Thu, 22 Jun 2006 09:20:13 -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 70731-02 for ; Thu, 22 Jun 2006 09:20:02 -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 AB2709F9ED6 for ; Thu, 22 Jun 2006 09:20:03 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.aeccom.com (Postfix) with ESMTP id CF1BF1C72C; Thu, 22 Jun 2006 14:20:01 +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 29787-01-61; Thu, 22 Jun 2006 14:20:00 +0200 (CEST) Received: from [192.168.2.140] (unknown [192.168.2.140]) by mail.aeccom.com (Postfix) with ESMTP id ED1041C71E; Thu, 22 Jun 2006 14:20:00 +0200 (CEST) Message-ID: <449A8AEE.50701@aeccom.com> Date: Thu, 22 Jun 2006 14:19:58 +0200 From: Sven Geisler Organization: AEC/communications GmbH User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: nicky 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> <449A88CA.3000108@valuecare.nl> In-Reply-To: <449A88CA.3000108@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/443 X-Sequence-Number: 19800 Hi Nick, I'm not that good to advice how to get PostgreSQL to use an index to get your results faster. Did you try "not (substr(t0.code,1,2) in ('14','15','16','17'))"? Cheers Sven. nicky schrieb: > 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. >