From: Surfing <onlinesurfing@gmail.com>
To: Misa Simic <misa.simic@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Efficiency Problem
Date: Sun, 17 Mar 2013 12:43:16 +0100
Message-ID: <5145AC54.7090009@gmail.com> (raw)
In-Reply-To: <4104469048777145945@unknownmsgid>
References: <4104469048777145945@unknownmsgid>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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 <mailto: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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: onlinesurfing@gmail.com, misa.simic@gmail.com
Subject: Re: Efficiency Problem
In-Reply-To: <5145AC54.7090009@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox