"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'
    return substr($1,2,2);
' language 'plpgsql'

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

Reply via email to