Re: Using multidimensional indexes in ordinal queries

On Mon, Jun 21, 2010 at 5:19 AM, Thom Brown <thombrown@gmail.com> wrote:
> I can't answer this, but is anyone else able to provide Alexander some feedback?

It seems like you can get more or less the same benefit from a
multicolumn btree index.  On my system, with the individual btree
indices, the query ran in 7625 ms; with an additional index on (v1,
v2, v3), it ran in 94 ms.  I didn't get the same plans as Alexander
did, though, so it may not really be apples to apples.  See attached
session trace.

Having said that, I'm very interested in hearing what other ideas
people have for using indices to speed up "ORDER BY" operations.
Currently, we support only "ORDER BY <indexed-value>".  KNNGIST will
allow "ORDER BY <indexed-value> <op> <constant>", but why stop there?
In theory, an index might know how to order the data by any arbitrary
expression the user might happen to enter.  If the user asks for
"ORDER BY (<indexed-value> <op1> <constan1t>) <op2> <constan2t>", who
is to say that we can't use an index scan to get that ordering
quickly?  (As a trivial case, suppose both ops are +, but there could
easily be more interesting ones.)  Or what about "ORDER BY
somefunc(<indexed-value>)"?  The trouble is that it's hard to think of
a way of teaching the planner about these cases without hard-coding
lots and lots of special-case kludges into the planner.  Still, if
someone has a clever idea...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company