"Yaroslav Mazurak" <[EMAIL PROTECTED]> > 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' ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly