Re: knngist - 0.8

2010/12/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> What we have at this point (pending contrib/btree_gist fixes) is
> nearest-neighbor searching capability for point columns.  And
> trigram-based nearest-neighbor for text strings, if you install
> contrib/pg_trgm.  That doesn't seem like a lot of return for the
> amount of work that went into it.  Are there plans to add KNN support
> for any other standard types?

Catching up tonight, I wonder I could propose to add ordering
operators in btree, not in gist, for basic types. So far, we couldn't
optimize simple examples like:

regression=# create index ti on t using btree(i);
CREATE INDEX
regression=# explain select * from t order by i - 10 limit 10;
QUERY PLAN
--------------------------------------------------------------------
Limit (cost=405.10..405.12 rows=10 width=24)
-> Sort (cost=405.10..430.10 rows=10000 width=24)
Sort Key: ((i - 10))
-> Seq Scan on t (cost=0.00..189.00 rows=10000 width=24)
(4 rows)

While this looks too stupid at a glance, adding 2 strategies into
btree, "addition" and "subtraction", will help RANGE concept on data
types; we were stacked around how to implement RANGE in both of window
functions' frame and PARTITION because of lack of "addition" and
"subtraction" idea. If we have 2 more strategies in btree, they can be
solved IMHO. I know addition and subtraction is never relevant to
btree indexing but avoiding unnecessary seq scan and supporting RANGE
concept may buy somehow.

Regards,

--
Hitoshi Harada