ORDER BY on DESC/ASC colums with INDEXes

  • Complexity Intelligence, LLC


       I know that when CREATE INDEX with DESC (the DESC) is practically ignored, but how to deal with a situation like the following ?

       table("priority" int, "date" timestamp with time zone)

       I need the older entry ("date") between the high priority rows ("priority"):
       SELECT * FROM "table" ORDER BY "priority" DESC, "insertTimestampZone" ASC LIMIT 1;

       Indexes don't help.

        INDEX on "priority" -> NO HELP
        INDEX on "date" -> NO HELP
        INDEX on ("priority" DESC, "date") works fine in postgresql, but desc is ignored in HSQLDB so it doesn't help…

        so query is too slow on million rows table.

    How to deal with this situazionion without DESC/ASC indexes and ORDER BY?


  • Complexity Intelligence, LLC

    a typo, the slow query is:

    SELECT * FROM "table" ORDER BY "priority" DESC, "date" ASC LIMIT 1;

  • Fred Toussi

    Fred Toussi - 2010-07-21

    As you figured out, it is not possible to create a single index directly. Therefore you need the complement or inverse of priority in the table.

    If priority is an integer in a limited range, say (1-10), define the complement as (9 - 0), then you can create and index on the two columns and use both SELECT * FROM "table" ORDER BY "priorityCOMPLEMENT" ASC, "date" ASC LIMIT 1;

    You can use a computed column in the form of


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks