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

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