CREATE INDEX with order clause - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size:
10.0pt;font-family:Arial">I would like to create an index on a table, specifying an order clause for one of the
columns.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="blue"
face="CourierNew" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"; 
color:blue">CREATE INDEX </span></font><font face="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC</span></font><p
class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">  <font color="blue"><span style="color:blue">ON
</span></font>GSLOG_EVENT(PLAYER_USERNAME,</span></font><pclass="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"">                
EVENT_NAME,</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-CA"style="font-size:10.0pt;font-family:"Courier New"">                 EVENT_DATE_CREATED <font
color="blue"><spanstyle="color:blue">DESC</span></font>);</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-CA" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial">which is not a valid, as the order clause </span></font><font face="Courier" size="2"><span
lang="EN-CA"style="font-size:10.0pt;font-family:Courier">DESC</span></font><font face="Arial" size="2"><span
lang="EN-CA"style="font-size:10.0pt;font-family:Arial"> is not supported.  Such as index would improve performance of
querylike:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="blue"
face="CourierNew" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"; 
color:blue">SELECT </span></font><font face="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">GAME_CLIENT_VERSION</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">  <font color="blue"><span style="color:blue">FROM
</span></font>GSLOG_EVENT</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanlang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"">  <font color="blue"><span
style="color:blue">WHERE</span></font>PLAYER_USERNAME = ?</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">    <font color="blue"><span style="color:blue">AND
</span></font>EVENT_NAME= ?</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanlang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"">    <font color="blue"><span
style="color:blue">AND</span></font>EVENT_DATE_CREATED < ?</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">  <font color="blue"><span style="color:blue">ORDER BY
</span></font>EVENT_DATE_CREATED<font color="blue"><span style="color:blue">DESC</span></font></span></font><p
class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">  LIMIT 1</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-CA" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial">Actually, I’m not sure that is useful; perhaps PostgreSQL handles pretty well such query
usingan index such as:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="blue"
face="CourierNew" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"; 
color:blue">CREATE INDEX </span></font><font face="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC</span></font><p
class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span lang="EN-CA"
style="font-size:10.0pt;font-family:"CourierNew"">  <font color="blue"><span style="color:blue">ON
</span></font>GSLOG_EVENT(PLAYER_USERNAME,</span></font><pclass="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span lang="EN-CA" style="font-size:10.0pt;font-family:"Courier New"">                
EVENT_NAME,</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
lang="EN-CA"style="font-size:10.0pt;font-family:"Courier New"">                 EVENT_DATE_CREATED);</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-CA" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial">Any idea?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial">--</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-CA" style="font-size: 
10.0pt;font-family:Arial">Daniel CAUNE</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-CA" style="font-size: 
10.0pt;font-family:Arial">Ubisoft Online Technology</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-CA" style="font-size: 
10.0pt;font-family:Arial">(514) 4090 2040 ext. 5418</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-CA" style="font-size:12.0pt"> </span></font></div>