[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: Postgresql equal join on function with columns not use index
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2023-06-15 8:35:47
Message-ID: CAFj8pRC6hHew+=rOUQEq+gLJ+HL=229oHN30wxUgsPubrL0bUg () mail ! gmail ! com
[Download RAW message or body]
Hi
čt 15. 6. 2023 v 10:32 odesílatel James Pang (chaolpan) <chaolpan@cisco.com>
napsal:
> Thanks a lot, we use orafce 3.17, and there some varchar2 columns and
> function indexes depends on oracle.substr too. Is it ok to upgrade to
> orafce version 4.4 by "alter extension update to ‘4.4'? it's online to do
> that ?
>
I didn't release 4.4, but it is available on github. Orafce supports
online upgrades
Hot fix can be execution of
https://github.com/orafce/orafce/blob/master/orafce--4.3--4.4.sql file
Regards
Pavel
>
> Thanks,
>
>
>
> James
>
>
>
> *From:* Pavel Stehule <pavel.stehule@gmail.com>
> *Sent:* Tuesday, June 13, 2023 11:01 PM
> *To:* Tom Lane <tgl@sss.pgh.pa.us>
> *Cc:* James Pang (chaolpan) <chaolpan@cisco.com>;
> pgsql-performance@lists.postgresql.org
> *Subject:* Re: Postgresql equal join on function with columns not use
> index
>
>
>
>
>
>
>
> út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
> napsal:
>
>
>
>
>
> út 13. 6. 2023 v 15:50 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>
> "James Pang (chaolpan)" <chaolpan@cisco.com> writes:
> > Looks like it's the function "regexp_replace" volatile and
> restrict=false make the difference, we have our application role with
> default search_path=oracle,$user,public,pg_catalog.
> > =# select
> oid,proname,pronamespace::regnamespace,prosecdef,proisstrict,provolatile
> from pg_proc where proname='regexp_replace' order by oid;
> > oid | proname | pronamespace | prosecdef | proisstrict |
> provolatile
> >
> -------+----------------+--------------+-----------+-------------+-------------
> > 2284 | regexp_replace | pg_catalog | f | t | i
> > 2285 | regexp_replace | pg_catalog | f | t | i
> > 17095 | regexp_replace | oracle | f | f | v
> > 17096 | regexp_replace | oracle | f | f | v
> > 17097 | regexp_replace | oracle | f | f | v
> > 17098 | regexp_replace | oracle | f | f | v
>
> Why in the world are the oracle ones marked volatile? That's what's
> preventing them from being used in index quals.
>
>
>
> It looks like orafce issue
>
>
>
> I'll fix it
>
>
>
> should be fixed in orafce 4.4.
>
>
>
> Regards
>
>
>
> Pavel
>
>
>
>
>
> Regards
>
>
>
> Pavel
>
>
>
>
> regards, tom lane
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><div>Hi<br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">čt 15. 6. 2023 v 10:32 odesílatel James Pang (chaolpan) <<a \
href="mailto:chaolpan@cisco.com">chaolpan@cisco.com</a>> \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
class="msg528833189958454138">
<div style="overflow-wrap: break-word;" lang="EN-US">
<div class="m_528833189958454138WordSection1">
<p class="MsoNormal"> Thanks a lot, we use orafce 3.17, and there some varchar2 \
columns and function indexes depends on oracle.substr too. Is it ok to upgrade to \
orafce version 4.4 by "alter extension update to ‘4.4'? it's online to do that \
?</p></div></div></div></blockquote><div><br></div><div>I didn't release 4.4, \
but it is available on github. Orafce supports online \
upgrades</div><div><br></div><div>Hot fix can be execution of <a \
href="https://github.com/orafce/orafce/blob/master/orafce--4.3--4.4.sql">https://github.com/orafce/orafce/blob/master/orafce--4.3--4.4.sql</a> \
file</div><div><br></div><div>Regards</div><div><br></div><div>Pavel<br></div><div><br></div><div><br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div class="msg528833189958454138"><div \
style="overflow-wrap: break-word;" lang="EN-US"><div \
class="m_528833189958454138WordSection1"><p class="MsoNormal"><u></u><u></u></p> <p \
class="MsoNormal"><u></u> <u></u></p> <p class="MsoNormal">Thanks,<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">James<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<div style="border-color:rgb(225,225,225) currentcolor \
currentcolor;border-style:solid none none;border-width:1pt medium medium;padding:3pt \
0in 0in"> <p class="MsoNormal"><b>From:</b> Pavel Stehule <<a \
href="mailto:pavel.stehule@gmail.com" target="_blank">pavel.stehule@gmail.com</a>> \
<br> <b>Sent:</b> Tuesday, June 13, 2023 11:01 PM<br>
<b>To:</b> Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us" \
target="_blank">tgl@sss.pgh.pa.us</a>><br> <b>Cc:</b> James Pang (chaolpan) <<a \
href="mailto:chaolpan@cisco.com" target="_blank">chaolpan@cisco.com</a>>; <a \
href="mailto:pgsql-performance@lists.postgresql.org" \
target="_blank">pgsql-performance@lists.postgresql.org</a><br> <b>Subject:</b> Re: \
Postgresql equal join on function with columns not use index<u></u><u></u></p> </div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule <<a \
href="mailto:pavel.stehule@gmail.com" target="_blank">pavel.stehule@gmail.com</a>> \
napsal:<u></u><u></u></p> </div>
<blockquote style="border-color:currentcolor currentcolor currentcolor \
rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium \
1pt;padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"> <div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">út 13. 6. 2023 v 15:50 odesílatel Tom Lane <<a \
href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>> \
napsal:<u></u><u></u></p> </div>
<blockquote style="border-color:currentcolor currentcolor currentcolor \
rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium \
1pt;padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"> <p \
class="MsoNormal">"James Pang (chaolpan)" <<a \
href="mailto:chaolpan@cisco.com" target="_blank">chaolpan@cisco.com</a>> \
writes:<br> > Looks like it's the function "regexp_replace" \
volatile and restrict=false make the difference, we have our application role with \
default search_path=oracle,$user,public,pg_catalog. <br>
> =# select \
oid,proname,pronamespace::regnamespace,prosecdef,proisstrict,provolatile from pg_proc \
where proname='regexp_replace' order by oid;<br> > oid | \
proname | pronamespace | prosecdef | proisstrict | provolatile<br> > \
-------+----------------+--------------+-----------+-------------+-------------<br> \
> 2284 | regexp_replace | pg_catalog | f | t \
| i<br> > 2285 | regexp_replace | pg_catalog | f | t \
| i<br> > 17095 | regexp_replace | oracle | f | f \
| v <br> > 17096 | regexp_replace | oracle | f | f \
| v<br> > 17097 | regexp_replace | oracle | f | f \
| v<br> > 17098 | regexp_replace | oracle | f | f \
| v<br> <br>
Why in the world are the oracle ones marked volatile? That's what's<br>
preventing them from being used in index quals.<u></u><u></u></p>
</blockquote>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">It looks like orafce issue<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">I'll fix it<u></u><u></u></p>
</div>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">should be fixed in orafce 4.4.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Regards<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Pavel<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<blockquote style="border-color:currentcolor currentcolor currentcolor \
rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium \
1pt;padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"> <div>
<div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Regards<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Pavel<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<blockquote style="border-color:currentcolor currentcolor currentcolor \
rgb(204,204,204);border-style:none none none solid;border-width:medium medium medium \
1pt;padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"> <p class="MsoNormal" \
style="margin-bottom:12pt"><br> regards, tom lane<br>
<br>
<u></u><u></u></p>
</blockquote>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</div></blockquote></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic