Thread: CREATE INDEX with order clause

<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> 

"Daniel Caune" <daniel.caune@ubisoft.com> writes:
> I would like to create an index on a table, specifying an order clause
> for one of the columns.

Search the archives for discussions of reverse-sort operator classes
(you might also get hits on the shorthand "opclass").
        regards, tom lane

On Wed, 2006-02-01 at 10:46 -0500, Daniel Caune wrote:
> Hi,
> 
[snip need for reverse-sort operator class]

> 
> SELECT GAME_CLIENT_VERSION
>   FROM GSLOG_EVENT
>   WHERE PLAYER_USERNAME = ?
>     AND EVENT_NAME = ?
>     AND EVENT_DATE_CREATED < ?
>   ORDER BY EVENT_DATE_CREATED DESC
>   LIMIT 1

>  
> 
> Actually, I’m not sure that is useful; perhaps PostgreSQL handles
> pretty well such query using an index such as:
> CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC
>   ON GSLOG_EVENT(PLAYER_USERNAME,
>                  EVENT_NAME,
>                  EVENT_DATE_CREATED);
>  
> 
> Any idea?

does index work with: SELECT GAME_CLIENT_VERSION     FROM GSLOG_EVENT    WHERE PLAYER_USERNAME = ?      AND EVENT_NAME
=?      AND EVENT_DATE_CREATED < ?    ORDER BY PLAYER_USERNAM DESC,             EVENT_NAME DESC,
EVENT_DATE_CREATEDDESC    LIMIT 1
 

gnari