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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Roussel | 2006-06-22 13:03:47 | Re: [HACKERS] Sun Donated a Sun Fire T2000 to the PostgreSQL |
Previous Message | Magnus Hagander | 2006-06-22 12:17:29 | Re: Speeding up query, Joining 55mil and 43mil records. |