Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UHBw3-0005IB-3N for pgsql-sql@arkaria.postgresql.org; Sun, 17 Mar 2013 11:39:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UHBw1-0003Jy-Of for pgsql-sql@arkaria.postgresql.org; Sun, 17 Mar 2013 11:39:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UHBvz-0003Jn-Jz for pgsql-sql@postgresql.org; Sun, 17 Mar 2013 11:39:27 +0000 Received: from mail-qa0-f42.google.com ([209.85.216.42]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UHBvv-0006ri-Hj for pgsql-sql@postgresql.org; Sun, 17 Mar 2013 11:39:26 +0000 Received: by mail-qa0-f42.google.com with SMTP id cr7so1164972qab.1 for ; Sun, 17 Mar 2013 04:39:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:mime-version:from:date:message-id:subject:to :content-type; bh=koczZXLqVtLSbWtzbiNkeBMuK7JguiYmN6JUT52xWzg=; b=zPF0sbQ7PvEjwnxxnUp1dp+kvF1K/CVDDtkJOZEUYaIiQQS7ZN3j40QLpNTAhuim+S GDDDHMNUphKC3ha3lceG8Ds4Vbe5xtfxEqgRXM7XvfYoUxMkpMGFB3aHi0Aw5Wj+pMjn 5gGVtFdLKBMVGBHk2htHvEL6ithiMwVkUVIMniGauyeo0VBXdIkGbGa04oFwOkIV2ysG MpeVNg1E9Di6bYQfnaP+I79H2PgW4D2Lvzr1k/XJGGGIDhfaB5Gctq0N5++bG2pCndKy e1JbjH8swT8KzEKFygwShfzBoanxDn+cgFS29qaw51MGn3uF2f6kxKV0xQMf2f4mZ6IJ lWgw== X-Received: by 10.224.178.77 with SMTP id bl13mr14853291qab.13.1363520361494; Sun, 17 Mar 2013 04:39:21 -0700 (PDT) MIME-Version: 1.0 From: Misa Simic Date: Sun, 17 Mar 2013 04:39:21 -0700 Message-ID: <4104469048777145945@unknownmsgid> Subject: Re: Efficiency Problem To: Surfing , "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=20cf302ef79eca7fad04d81d52df X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --20cf302ef79eca7fad04d81d52df Content-Type: text/plain; charset=UTF-8 Hi, 1) Is function marked as immutable? 2) if immutable doesnt help... It should be possible execute it first, and use it in other dynamics things in where... Cheers, Misa Sent from my Windows Phone ------------------------------ From: Surfing Sent: 17/03/2013 12:16 To: pgsql-sql@postgresql.org Subject: [SQL] Efficiency Problem Hi all, I'm composing a query from a web application of type: *SELECT * FROM table WHERE a_text_field LIKE replace_something ('%** a_given_string**%');* The function replace_something( ... ) is a stored procedure that replaces some particular characters with others. The problem is that I noticed that this query is inefficient... and I think that the replace_something ( ... ) function is called for each row of the table. This observation is motivated by the fact that it takes around 30 seconds to execute on the table (of about 25,000 rows), whereas if I execute: *SELECT * FROM table WHERE a_text_field LIKE '**pre_processed_string**** ';* where* pre_processed_string** *is the result of the application of replace_something ('%*a_given_string*%') it just takes 164ms. The execution of *SELECT replace_something ('%**a_given_string**%')* takes only 14ms. Summarizing, - Replace function: 14ms - SELECT query without replace function: 164ms - SELECT query with replace function: 30.000ms Morever, I cannot create a stored procedure that precalculate the *pre_processed_string *and executes the query, since I dinamically compose other conditions in the WHERE clause. Any suggestion? Thank you. ** --20cf302ef79eca7fad04d81d52df Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

1) Is function marked as immutable?

2) if i= mmutable doesnt help... It should be possible execute it first, and use it = in other dynamics things in where...

Cheers,

Misa

Sent from my Windows Phone
From: Surfing
Sent: 17/03/2013 12:16
To: pg= sql-sql@postgresql.org
Subject: [SQL] Efficiency Problem

Hi all,
=C2=A0=C2=A0=C2=A0 I'm composing a query from a web application of = type:

=C2=A0=C2=A0=C2=A0 SELECT * FROM table WHERE a_text_field LIKE replace_something ('%a_given_string%');<= /b>

The function replace_something( ... ) is a stored procedure that replaces some particular characters with others.
The problem is that I noticed that this query is inefficient... and I think that the replace_something ( ... ) function is called for each row of the table.

This observation is motivated by the fact that it takes around 30 seconds to execute on the table (of about 25,000 rows), whereas if I execute:
=C2=A0=C2=A0=C2=A0 SELECT * FROM table WHERE a_text_field LIKE '= pre_processed_string';

where pre_processed_string is the result of the application of replace_something ('%a_given_string%')=C2= =A0 it just takes 164ms.

The execution of
=C2=A0=C2=A0=C2=A0 SELECT replace_something ('%a_given= _string%')
=C2=A0takes only 14ms.

Summarizing,
- Replace function: =C2=A0=C2=A0=C2=A0 14ms
- SELECT query without replace function: =C2=A0=C2=A0=C2=A0 164ms
- SELECT query with replace function:=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 30.= 000ms

Morever, I cannot create a stored procedure that precalculate the pr= e_processed_string and executes the query, since I dinamically
compose other conditions in the WHERE clause.

Any suggestion?

Thank you.
--20cf302ef79eca7fad04d81d52df--