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

Question

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 <->?

jjanes · Accepted Answer · 2021-11-19 19:31:23Z

2

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);

answered Nov 19, 2021 at 19:31

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related