Indexing issue in postgres

Question

It is impossible to speed up the database due to indexing.

I create a table:

CREATE TABLE IF NOT EXISTS coordinate( Id serial primary key,
                                   Lat DECIMAL(9,6),
                                   Lon DECIMAL(9,6));

After that I add indexing:

CREATE INDEX indeLat ON coordinate(Lat);
CREATE INDEX indeLon ON coordinate(Lon);

Then the table is filled in:

INSERT INTO coordinate (Lat, Lon) VALUES(48.685444, 44.474254);

Fill in 100k random coordinates.

Now I need to return all coordinates that are included in a radius of N km from a given coordinate.

SELECT id, Lat, Lon
FROM coordinate
WHERE acos(sin(radians(48.704578))*sin(radians(Lat)) + cos(radians(48.704578))*cos(radians(Lat))*cos(radians(Lon)-radians(44.507112))) * 6371 < 50;

The test execution time is approximately 0.2 seconds, and if you do not do CREATE INDEX, the time does not change. I suspect that there is an error in the request, maybe you need to rebuild it somehow?

I'm sorry for my english

Laurenz Albe · Accepted Answer · 2020-08-27 13:10:18Z

1

An index can only be used if the indexed expression is exactly what you have on the non-constant side of the operator. That is obviously not the case here.

For operations like this, you need to use the PostGIS extension. Then you can define a table like:

CREATE TABLE coordinate (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   p geography NOT NULL
);

and query like this:

SELECT id, p
FROM coordinate
WHERE ST_DWithin(p, 'POINT(48.704578 44.507112)'::geography, 50);

This index would speed up the query:

CREATE INDEX ON coordinate USING gist (p);

answered Aug 27, 2020 at 13:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Indexing issue in postgres

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