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

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

If this is the only query that you're having problems with, you might be
helped with a partial index - depending on how much 14-17 really
filters. Try something like:

CREATE INDEX foo ON src.src_faktuur_verrsec (id) WHERE
substr(t0.code,1,2) not in ('14','15','16','17') AND
(substr(t0.correctie,4,1) <> '1' OR t0.correctie is null)

That index shuold be usable for the JOIN while filtering out all the
unnecessary rows before you even get tehre.
In the same way, if it filters a lot of rows, you might want to try
CREATE INDEX foo ON src.src_faktuur_verricht (id) WHERE EXTRACT(YEAR
from t1.datum) > 2004


But this kind of requires that the partial indexes actually drop
significant amounts of the table. If not, then they'll be of no help.

//Magnus