ORDER BY on DESC/ASC colums with INDEXes

2010-07-21
2014-01-19
  • Hi,

       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?

      

     
  • 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
    priorityCOMPLEMENT INT GENERATED ALWAYS AS (10 - PRIORITY)