Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UHC0P-0005qe-Nm for pgsql-sql@arkaria.postgresql.org; Sun, 17 Mar 2013 11:44:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UHC0P-0004Fj-8P for pgsql-sql@arkaria.postgresql.org; Sun, 17 Mar 2013 11:44:01 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UHC0O-0004Fd-1D for pgsql-sql@postgresql.org; Sun, 17 Mar 2013 11:44:00 +0000 Received: from mail-bk0-x22a.google.com ([2a00:1450:4008:c01::22a]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UHC0L-0001Gj-5f for pgsql-sql@postgresql.org; Sun, 17 Mar 2013 11:43:59 +0000 Received: by mail-bk0-f42.google.com with SMTP id jk7so2097059bkc.15 for ; Sun, 17 Mar 2013 04:43:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=x-received:message-id:date:from:user-agent:mime-version:to:cc :subject:references:in-reply-to:content-type; bh=p/VhCz1NPaHl6eJ75XRdTiMAp4qWe+O/gnznDyWCHKI=; b=H2DZ59PAM+QvKZLSoh0IqtWKJclX+tvfhZE0mnGtdlBwUXd7lbWJMHmJxwUGPAtLKG WSOz6Y82EZOOeOnttaKVPeqTBHJZ1Ah0STOBy0e0TcIpUWyFxm7pbKe1T22d1I7EqAsb OpkL11195HMMdyFbUmKs9RDeWLoYCKoNRmTQ+zTme3F9TPv9xZxfLU4CUDpTLWJTv6qI 18jiKwnsmbmis19vK3/ey+XSdalCdBJWltl8oYAFXhvj/XJO1WoYyU4TlpnAtkfH1m83 d11xRq/ow9wg1OVNrlv/cghgvskIuoV+Vm5qfpzi3PCHy0Nq0E7VwA7Er7ExZgwKpBPk Z18Q== X-Received: by 10.204.246.193 with SMTP id lz1mr5447894bkb.120.1363520634527; Sun, 17 Mar 2013 04:43:54 -0700 (PDT) Received: from [192.168.0.2] (adsl-ull-75-208.50-151.net24.it. [151.50.208.75]) by mx.google.com with ESMTPS id n1sm3510574bkv.14.2013.03.17.04.43.52 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sun, 17 Mar 2013 04:43:53 -0700 (PDT) Message-ID: <5145AC54.7090009@gmail.com> Date: Sun, 17 Mar 2013 12:43:16 +0100 From: Surfing User-Agent: Mozilla/5.0 (Windows NT 6.2; WOW64; rv:17.0) Gecko/20130307 Thunderbird/17.0.4 MIME-Version: 1.0 To: Misa Simic CC: "pgsql-sql@postgresql.org" Subject: Re: Efficiency Problem References: <4104469048777145945@unknownmsgid> In-Reply-To: <4104469048777145945@unknownmsgid> Content-Type: multipart/alternative; boundary="------------070109010408020908070802" X-Pg-Spam-Score: -2.0 (--) 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 This is a multi-part message in MIME format. --------------070109010408020908070802 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit IMMUTABLE solved the problem. Thank you! Il 17/03/2013 12.39, Misa Simic ha scritto: > 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. --------------070109010408020908070802 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit IMMUTABLE solved the problem.

Thank you!

Il 17/03/2013 12.39, Misa Simic ha scritto:
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.

--------------070109010408020908070802--