From: Sven Geisler <sgeisler@aeccom.com>
To: nicky <nicky@valuecare.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: Speeding up query, Joining 55mil and 43mil records.
Date: Thu, 22 Jun 2006 14:19:58 +0200
Message-ID: <449A8AEE.50701@aeccom.com> (raw)
In-Reply-To: <449A88CA.3000108@valuecare.nl>
References: <44994DE7.5070801@valuecare.nl>
<20060621184803.GC93655@pervasive.com>
<449A677D.4020504@valuecare.nl>
<449A7F25.3040607@aeccom.com>
<449A88CA.3000108@valuecare.nl>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sgeisler@aeccom.com, nicky@valuecare.nl
Subject: Re: Speeding up query, Joining 55mil and 43mil records.
In-Reply-To: <449A8AEE.50701@aeccom.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox