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.
