Обсуждение: CREATE INDEX with order clause
Hi,
I would like to create an index on a table, specifying an order clause for one of the columns.
CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC
ON GSLOG_EVENT(PLAYER_USERNAME,
EVENT_NAME,
EVENT_DATE_CREATED DESC);
which is not a valid, as the order clause DESC is not supported. Such as index would improve performance of query like:
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?
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
"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