Gist index in PostgreSQL only works on order, but not on where predicate-CodePudding

I've got a plain table with LatLon column containing a point with object location in space with GiST index created on this column:

create table "Machine"
(
    "MachineId" integer not null,
    "Name" text,
    "Description" text default ''::text,
    "LatLon" point default point((0)::double precision, (0)::double precision) not null
);

create index on "Machine" using gist("LatLon");

I've got a query on selecting only points within some distance to the point with fixed coordinates:

select * from "Machine" where "LatLon" <-> point(25.123456789,-60.123456789) < 100;

Although explain on such query shows the index is not being used during execution:

Seq Scan on "Machine"  (cost=0.00..244762.46 rows=1753121 width=208)
  Filter: (("LatLon" <-> '(25.123456789,-60.123456789)'::point) < '100'::double precision)
  Rows Removed by Filter: 5259364

At the same time, executing a canonical order by query against LatLon column shows the index works perfectly:

select * from "Machine" order by "LatLon" <-> point(25.123456789,-60.123456789);

Index Scan using "Machine_LatLon_idx" on "Machine"  (cost=0.41..1021907.70 rows=5259364 width=216)
  Order By: ("LatLon" <-> '(25.123456789,-60.123456789)'::point)

Why the GiST index doesn't apply on where statement with distance operator <->?

CodePudding user response：

Because someone implemented one, and not the other.

Note that this is not simply an operator, and that no other operator pairings (<-> and <, here) use indexes, either. So you would have to introduce the infrastructure to support such indexes, as well as this specific implementation.

If you just want the close thing with an index, you need to pull the distance into the point to get a circle. Then you have a nice binary operator for the index to grab onto:

select * from "Machine" where "LatLon" <@ circle(point(25.123456789,-60.123456789),100);