Skip site navigation (1) Skip section navigation (2)

Re: Speeding up query, Joining 55mil and 43mil records.

From: Sven Geisler <sgeisler(at)aeccom(dot)com>
To: nicky <nicky(at)valuecare(dot)nl>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Speeding up query, Joining 55mil and 43mil records.
Date: 2006-06-22 12:19:58
Message-ID: 449A8AEE.50701@aeccom.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.
> 

In response to

pgsql-performance by date

Next:From: David RousselDate: 2006-06-22 13:03:47
Subject: Re: [HACKERS] Sun Donated a Sun Fire T2000 to the PostgreSQL
Previous:From: Magnus HaganderDate: 2006-06-22 12:17:29
Subject: Re: Speeding up query, Joining 55mil and 43mil records.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group