[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Postgres delete performance problem
From:       Vitalii Tymchyshyn <tivv00 () gmail ! com>
Date:       2013-01-04 18:05:28
Message-ID: CABWW-d1kp9AVovSE-6yVCbpNLO0Rt_U2mW6ZCnzYUSCCtP3mpA () mail ! gmail ! com
[Download RAW message or body]

Yes, the issue was resolved by the method I proposed. You need to specify
correct type either on java-side or server-side (query text).
See my explanation (it seems it got out of the list):

The driver does not parse your query, so it simply passes everything to
server.
Server use widening conversion, so "bigint=number" becomes
"bigint::number=number", not "bigint=number::bigint" and index can't be
used when any function is applied to indexed field.
Note, that server can't do "bigint=number::bigint" because it does not know
the numbers you will pass.
Consider examples:
1) 0 = 123456789012345678901234567890
2) 0 = 0.4
Correct value is false, but "bigint=number::bigint" will give you
"overflow" error for the first example and true for the second, which is
incorrect.


2013/1/4 dankogan <dan@iqtell.com>

> Hello,
>
> Just wondering whether you were able to resolve this issue.
> We are experiencing a very similar issue with deletes using Postgrs 9.0.5
> on
> Ubuntu 12.04.
>
> Dan
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Postgres-delete-performance-problem-tp5714153p5738765.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



-- 
Best regards,
 Vitalii Tymchyshyn

[Attachment #3 (text/html)]

<div dir="ltr">Yes, the issue was resolved by the method I proposed. You need to \
specify correct type either on java-side or server-side (query text).<div style>See \
my explanation (it seems it got out of the list):</div><div style> <br \
style="font-family:arial,sans-serif;font-size:13.333333969116211px"><span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">The driver does \
not parse your query, so it simply passes everything to server.</span><br \
style="font-family:arial,sans-serif;font-size:13.333333969116211px"> <span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">Server use \
widening conversion, so &quot;bigint=number&quot; becomes \
&quot;bigint::number=number&quot;, not &quot;bigint=number::bigint&quot; and index \
can&#39;t be used when any function is applied to indexed field.</span><br \
style="font-family:arial,sans-serif;font-size:13.333333969116211px"> <span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">Note, that server \
can&#39;t do &quot;bigint=number::bigint&quot; because it does not know the numbers \
you will pass.</span><br \
style="font-family:arial,sans-serif;font-size:13.333333969116211px"> <span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">Consider \
examples:</span><br style="font-family:arial,sans-serif;font-size:13.333333969116211px"><span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">1) 0 = \
123456789012345678901234567890</span><br \
style="font-family:arial,sans-serif;font-size:13.333333969116211px"> <span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">2) 0 = \
0.4</span><br style="font-family:arial,sans-serif;font-size:13.333333969116211px"><span \
style="font-family:arial,sans-serif;font-size:13.333333969116211px">Correct value is \
false, but &quot;bigint=number::bigint&quot; will give you &quot;overflow&quot; error \
for the first example and true for the second, which is incorrect.</span><br \
style="font-family:arial,sans-serif;font-size:13.333333969116211px"> </div></div><div \
class="gmail_extra"><br><br><div class="gmail_quote">2013/1/4 dankogan <span \
dir="ltr">&lt;<a href="mailto:dan@iqtell.com" \
target="_blank">dan@iqtell.com</a>&gt;</span><br><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> Hello,<br>
<br>
Just wondering whether you were able to resolve this issue.<br>
We are experiencing a very similar issue with deletes using Postgrs 9.0.5 on<br>
Ubuntu 12.04.<br>
<br>
Dan<br>
<br>
<br>
<br>
--<br>
View this message in context: <a \
href="http://postgresql.1045698.n5.nabble.com/Postgres-delete-performance-problem-tp5714153p5738765.html" \
target="_blank">http://postgresql.1045698.n5.nabble.com/Postgres-delete-performance-problem-tp5714153p5738765.html</a><br>


Sent from the PostgreSQL - performance mailing list archive at Nabble.com.<br>
<span class="HOEnZb"><font color="#888888"><br>
<br>
--<br>
Sent via pgsql-performance mailing list (<a \
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>)<br>
 To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-performance" \
target="_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br> \
</font></span></blockquote></div><br><br clear="all"><div><br></div>-- <br>Best \
regards,<br> Vitalii Tymchyshyn </div>



[prev in list] [next in list] [prev in thread] [next in thread]