[postgis-devel] KNN Box Op <#>

[Paul Ramsey]

On Wed, Sep 28, 2011 at 2:44 AM, Paragon Corporation <lr at pcorp.us> wrote:

> I didn't see any apparent use in the plan saying anything about pulling from
> index etc?

If you look into the CREATE OPERATOR CLASS statement, you'll see
something new and interesting (inside the 9.1+ protection block) which
is that the <-> and <#> operators are defined with a special clause,
"FOR ORDER BY". The other operators are implicitly defined as "FOR
SEARCH", the default. Aha. So for these new ORDER BY operators, the
index gets put to use when they appear in the ORDER BY clause. Just as
for the other operators the index gets put to use when they appear in
the WHERE clause.

So,

EXPLAIN ANALYZE
SELECT * FROM mytable
ORDER BY geom <#> 'LINESTRING()'
LIMIT 10;

Best,

P.