Re: Indexes with descending date columns

From: Tom Lane
Subject: Re: Indexes with descending date columns
Date: ,
Msg-id: 27697.1143176353@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Indexes with descending date columns  (Theo Kramer)
List: pgsql-performance

Tree view

Indexes with descending date columns  (Theo Kramer, )
 Re: Indexes with descending date columns  (, )
  Re: Indexes with descending date columns  (Theo Kramer, )
   Re: Indexes with descending date columns  (Alvaro Herrera, )
    Array performance  (Ruben Rubio Rey, )
     Re: Array performance  ("Jim C. Nasby", )
      Re: Array performance  (Ruben Rubio Rey, )
       Re: Array performance  ("Jim C. Nasby", )
        Re: Array performance  (Michael Fuhr, )
         limitation using LIKE on ANY(array)  (K C Lau, )
          Re: limitation using LIKE on ANY(array)  (Tom Lane, )
           Re: limitation using LIKE on ANY(array)  (K C Lau, )
     Re: Array performance  (Tom Lane, )
      Re: Array performance  (Ruben Rubio Rey, )
   Re: Indexes with descending date columns  ("Jim C. Nasby", )
    Re: Indexes with descending date columns  (Theo Kramer, )
     Re: Indexes with descending date columns  ("Jim C. Nasby", )
      Re: Indexes with descending date columns  (Bruce Momjian, )
       Re: Indexes with descending date columns  (Markus Schaber, )
  Re: Indexes with descending date columns  (Theo Kramer, )
   Re: Indexes with descending date columns  (Tom Lane, )

Theo Kramer <> writes:
> If so, I would appreciate any pointers on where to start on this -
> already fumbling my way through Interfacing Extensions To Indexes in the
> manual...

Search the PG list archives for discussions of reverse-sort opclasses.
It's really pretty trivial, once you've created a negated btree
comparison function for the datatype.

This is the sort of thing that we are almost but not quite ready to put
into the standard distribution.  The issues that are bugging me have to
do with whether NULLs sort low or high --- right now, if you make a
reverse-sort opclass, it will effectively sort NULLs low instead of
high, and that has some unpleasant consequences because the rest of the
system isn't prepared for variance on the point (in particular I'm
afraid this could break mergejoins).  I'd like to see us make "NULLs
low" vs "NULLs high" be a defined property of opclasses, and deal with
the fallout from that, and then we could put reverse-sort opclasses for
all the standard datatypes into the regular distribution.

            regards, tom lane


pgsql-performance by date:

From: "Jim C. Nasby"
Date:
Subject: Re: Problem with query, server totally unresponsive
From: Alvaro Herrera
Date:
Subject: Re: WAL logging of SELECT ... INTO command