2010/12/4 Tom Lane <tgl@sss.pgh.pa.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=10000width=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