"Yaroslav Mazurak" <yamazurak@Lviv.Bank.Gov.UA>
> Problem is that SQL statement (see below) is running too long. With
> current WHERE clause 'SUBSTR(2, 2) IN ('NL', 'NM') return 25 records.
> With 1 record, SELECT time is about 50 minutes and takes approx. 120Mb
> RAM. With 25 records SELECT takes about 600Mb of memory and ends after
> about 10 hours with error: "Memory exhausted in AllocSetAlloc(32)".
Did you try to use a functional index on that field ?
create or replace function my_substr(varchar)
returns varchar AS'
begin
return substr($1,2,2);
end;
' language 'plpgsql'
IMMUTABLE;
create index idx on <table> ( my_substr(<field>) );
and after you should use in your where:
where my_substr(<field>) = 'NL'
