[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Efficiency Problem
From: Surfing <onlinesurfing () gmail ! com>
Date: 2013-03-17 11:43:16
Message-ID: 5145AC54.7090009 () gmail ! com
[Download RAW message or body]
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.
[Attachment #3 (text/html)]
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
IMMUTABLE solved the problem.<br>
<br>
Thank you!<br>
<br>
<div class="moz-cite-prefix">Il 17/03/2013 12.39, Misa Simic ha
scritto:<br>
</div>
<blockquote cite="mid:4104469048777145945@unknownmsgid" type="cite">
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<div>
<div style="font-family:Calibri,sans-serif;font-size:11pt">Hi,<br>
<br>
1) Is function marked as immutable?<br>
<br>
2) if immutable doesnt help... It should be possible execute
it first, and use it in other dynamics things in where...<br>
<br>
Cheers,<br>
<br>
Misa<br>
<br>
Sent from my Windows Phone<br>
</div>
</div>
<hr><span
style="font-family:Tahoma,sans-serif;font-size:10pt;font-weight:bold">From:
</span><span style="font-family:Tahoma,sans-serif;font-size:10pt">Surfing</span><br>
<span
style="font-family:Tahoma,sans-serif;font-size:10pt;font-weight:bold">Sent:
</span><span style="font-family:Tahoma,sans-serif;font-size:10pt">17/03/2013
12:16</span><br>
<span
style="font-family:Tahoma,sans-serif;font-size:10pt;font-weight:bold">To:
</span><span style="font-family:Tahoma,sans-serif;font-size:10pt"><a
moz-do-not-send="true" \
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a></span><br> <span
style="font-family:Tahoma,sans-serif;font-size:10pt;font-weight:bold">Subject:
</span><span style="font-family:Tahoma,sans-serif;font-size:10pt">[SQL]
Efficiency Problem</span><br>
<br>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
Hi all,<br>
I'm composing a query from a web application of type:<br>
<br>
<b>SELECT * FROM table WHERE a_text_field LIKE
replace_something ('%</b><b><i>a_given_string</i></b><b>%');</b><br>
<br>
The function replace_something( ... ) is a stored procedure that
replaces some particular characters with others.<br>
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.<br>
<br>
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:<br>
<b>SELECT * FROM table WHERE a_text_field LIKE \
'</b><b><i>pre_processed_string</i></b><b>';</b><br> <br>
where<i> pre_processed_string</i><i> </i>is the result of the
application of replace_something ('%<i>a_given_string</i>%') it
just takes 164ms.<br>
<br>
The execution of <br>
<b>SELECT replace_something \
('%</b><b><i>a_given_string</i></b><b>%')</b><br> takes only 14ms.<br>
<br>
Summarizing, <br>
- Replace function: 14ms<br>
- SELECT query without replace function: 164ms<br>
- SELECT query with replace function: 30.000ms<br>
<br>
Morever, I cannot create a stored procedure that precalculate the
<i>pre_processed_string </i>and executes the query, since I
dinamically <br>
compose other conditions in the WHERE clause.<br>
<br>
Any suggestion?<br>
<br>
Thank you.<br>
</blockquote>
<br>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic