CODEWITHSUNDEEP
postgresqlindexingquery-optimization
Jack R-G
Jack R-G

Reputation: 1896

PostgreSQL Column Index Query Optimization Question

Will the following query be optimizable if I create indexes on latitude*69 and longitude*46?

select * from locations where abs(latitude*69 - 3036) <= 25
                          and abs(longitude*46 - 8970) <= 25

Or must I remove the abs() and code it more like:

select * from locations where (latitude*69 - 3036) between -25 and 25 
                          and (longitude*46 - 8970) between -25 and 25

or the even more simplistic:

select * from locations where latitude*69 between 3036-25 and 3036+25 
                          and longitude*46 between 8970-25 and 8970+25

Upvotes: 1

Views: 513

Answers (2)

jperelli
jperelli

Reputation: 7197

To see if the query is optimizable with indexes, the easy way is to do what splash says.

Create a GIST INDEX and use EXPLAIN or EXPLAIN VERBOSE before a query to see the execution plan that postgres uses. If you see something like SCAN SEQ, then is not using the index. If you see a INDEX SCAN then is using the index for that particular query.

In order to use the indexes over GIS data, you must use functions that use the bounding box of the geom/geog. There are functions in Postgis that make use of bounding boxes to use the indexes, like all the operators,or some functions that use operators inside to filter.

I think that the best query you could make to see if the lonlat is in a box (is that what you are trying to do?) is this:

SELECT *
FROM locations
WHERE
    ST_Dwithin(
        'POINT('||(longitude*46)||' '||(latitude*69)||')'::geometry,
        'POINT(8970 3036)'::geometry
    )

(not tested, but should work also with indexes)

Upvotes: 4

unmounted
unmounted

Reputation: 34388

I get the sense from your question that you are looking to find points within a 25 unit box around some point. Any postgres index (GIN, GiST, b-tree, r-tree etc.) will do fine on this query as long as locations are only points. If locations include some other geometries (shapes of cell tower reception, delivery route areas, whatever), then you want a GiST r-tree. But a better approach overall, since I get the impression from some other questions that geography is fundamental to what you're working on, is to use PostGIS or at the very least the geometry support built into Postgres. A literal translation of your example would be: 

select * from locations 
where my_point_column <@ box '((194.4, 43.36),(194.5 43.6))'

in Postgres against a column with a geometry type, and the PostGIS version is much the same with a lot of improvements and utilities for all kinds of data.

Upvotes: 1

Related Questions