PostgreSQL / GIST index require "LIMIT" clau

When using PostgreSQL and POINT type / GIST indexing for location queries,"LIMIT" clause is required for correctly utilizing the GIST index.

CREATE TABLE points (
  name varchar(40) NOT NULL,type varchar(4) NOT NULL,loc POINT NOT NULL
);

CREATE INDEX pointdist ON points USING gist (loc);

INSERT INTO points (name,type,loc) VALUES 
('p1','loc',point '(151.192124,-33.888159)'),('p2',point '(151.181696,-33.885665)');

explain SELECT name,loc FROM points ORDER BY point '(151.192124,-33.888159)' <-> loc;
explain SELECT name,loc FROM points ORDER BY loc <-> point '(151.192124,-33.888159)' LIMIT 10; -- MUST HAVE LIMIT TO USE INDEX

CREATE TABLE points1 (
name varchar(40) NOT NULL,loc POINT NOT NULL
);

CREATE INDEX pointdist1 ON points1 USING gist (loc);

INSERT INTO points1 (name,loc FROM points1 ORDER BY point '(151.192124,-33.888159)' <-> loc LIMIT 10;
explain SELECT name,loc FROM points1 ORDER BY loc <-> point '(151.192124,-33.888159)' LIMIT 10;
                                    QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.99 rows=10 width=114)
   ->  Index Scan using pointdist1 on points1  (cost=0.00..55.45 rows=560 width=114)
         Order By: (loc <-> '(151.192124,-33.888159)'::point)
 
 
explain SELECT name,-33.888159)';
                            QUERY PLAN                            
------------------------------------------------------------------
 Sort  (cost=42.56..43.96 rows=560 width=114)
   Sort Key: ((loc <-> '(151.192124,-33.888159)'::point))
   ->  Seq Scan on points1  (cost=0.00..17.00 rows=560 width=114)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!