2014-10-05 11:33 GMT+04:00 Dmitry E. Oboukhov <unera@debian.org>:
Таблица "public.t" Колонка | Тип | Модификаторы ---------+---------+------------------------------------------------ id | integer | NOT NULL DEFAULT nextval('t_id_seq'::regclass) p | point | Индексы: "t_p_gist_idx" gist (p)
INSERT INTO "t" SELECT generate_series(1, 1000000) AS "id", point(random(), random()) AS "p";
EXPLAIN ANALYZE SELECT "id", point(0.3, 0.3) <-> p FROM "t" ORDER BY point(0.3, 0.3) <-> p LIMIT 10;
Limit (cost=0.29..1.10 rows=10 width=20) (actual time=0.248..0.276 rows=10 loops=1) -> Index Scan using t_p_gist_idx on t (cost=0.29..81860.29 rows=1000000 width=20) (actual time=0.246..0.269 rows=10 loops=1) Order By: (p <-> '(0.3,0.3)'::point) Total runtime: 0.320 ms (4 строки)
Для точек - прям то что надо, работает чистый index scan и лимит.
Продолжаем эксперимент:
ALTER TABLE "t" ADD COLUMN "c" cube NOT NULL DEFAULT cube(ARRAY[random(), random(), random(), random()]);
CREATE INDEX "t_c_gist_idx" ON "t" USING GIST ("c");
Далее выбираем ближайшие кубы по аналогии с точками
EXPLAIN ANALYZE SELECT "id", cube_distance("c", cube(ARRAY[0.3,0.3,0.3,0.3])) AS "dist" FROM "t" ORDER BY cube_distance("c", cube(ARRAY[0.3,0.3,0.3,0.3])) LIMIT 10;