Professional Web Applications Themes

multi column index and order by - PostgreSQL / PGSQL

Hello, "order by a asc b desc" how can I create an index for this? Mage ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]...

  1. #1

    Default multi column index and order by

    Hello,

    "order by a asc b desc"

    how can I create an index for this?

    Mage

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Mage Guest

  2. #2

    Default Re: multi column index and order by


    On Wed, 5 Jan 2005, Mage wrote:
    > "order by a asc b desc"
    >
    > how can I create an index for this?
    You need to create an operator class for reversed comparison of whatever
    type b is and then use it on b in the index definition.

    Something like (however, you should really use a C function for the
    reverse comparison function rather than the sql one shown here):

    create function btint4cmprev(integer, integer) returns integer as 'select
    btint4cmp($2,$1);' language 'sql';

    CREATE OPERATOR CLASS int4_desc_ops FOR TYPE int4 USING BTREE AS
    OPERATOR 1 >, OPERATOR 2 >=, OPERATOR 3 =, OPERATOR 4 <=, OPERATOR 5 <,
    FUNCTION 1 btint4cmprev(int4,int4);

    which you could then use something like:
    create index testindex on testtable(a, b int4_desc_ops);

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Stephan Szabo Guest

  3. #3

    Default Re: multi column index and order by

    On Wed, Jan 05, 2005 at 15:35:22 +0100,
    Mage <magemage.hu> wrote:
    > Hello,
    >
    > "order by a asc b desc"
    >
    > how can I create an index for this?
    Currently you can't directly.

    In recent versions of Postgres you have some options:

    If one of the types has a normal minus operator, then you can use a functional
    index using the minus operator that will allow you to get the ordering you
    want. You will have to change the queries to use that operator explicitly
    as the optimizer won't be able to figure this out on its own.

    You can make a new operator class that defines ordering in the opposite
    direction and then use that opclass when defining the index. I believe that
    you also need to specify the opclass in the ORDER BY clause when doing this.

    As a varient on the first case, you could make a function that returns
    values that can be used for sorting. The output values need not be the
    same type as the input values. For example you might convert dates
    to the negative of the julian day number.

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Bruno Wolff III Guest

Similar Threads

  1. How do I get the end word-index of a multi-page selection?
    By Gitte_Thorvil@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 1
    Last Post: January 14th, 12:39 PM
  2. How to Populate multi column datagrid
    By leotemp in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: June 26th, 01:39 AM
  3. Delete with a multi-column join?
    By leon-pg@comvision.com in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 25th, 10:03 PM
  4. Multi-column indexes
    By Edmund Dengler in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 16th, 12:00 AM
  5. Multi Column List Box
    By John Devine in forum Microsoft Access
    Replies: 0
    Last Post: July 8th, 12:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139