Re: PostgreSQL performance problem -> tuning

"Yaroslav Mazurak" <yamazurak(at)Lviv(dot)Bank(dot)Gov(dot)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'