pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE INDEX with order clause
3+ messages / 3 participants
[nested] [flat]
* CREATE INDEX with order clause
@ 2006-02-01 15:46 Daniel Caune <daniel.caune@ubisoft.com>
2006-02-01 16:16 ` Re: CREATE INDEX with order clause Tom Lane <tgl@sss.pgh.pa.us>
2006-02-03 09:27 ` Re: CREATE INDEX with order clause Ragnar <gnari@hive.is>
0 siblings, 2 replies; 3+ messages in thread
From: Daniel Caune @ 2006-02-01 15:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CREATE INDEX with order clause
2006-02-01 15:46 CREATE INDEX with order clause Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-02-01 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-02-01 16:16 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CREATE INDEX with order clause
2006-02-01 15:46 CREATE INDEX with order clause Daniel Caune <daniel.caune@ubisoft.com>
@ 2006-02-03 09:27 ` Ragnar <gnari@hive.is>
1 sibling, 0 replies; 3+ messages in thread
From: Ragnar @ 2006-02-03 09:27 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
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_CREATED DESC
LIMIT 1
gnari
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-02-03 09:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-01 15:46 CREATE INDEX with order clause Daniel Caune <daniel.caune@ubisoft.com>
2006-02-01 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-03 09:27 ` Ragnar <gnari@hive.is>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox