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.
>