PostgreSQL Column Index Query Optimization Question

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

What, and do some actual work? ;) I can do what you're asking, but if it doesn't optimize it then I have no clue how to change it so that it will optimize. I couldn't find any docs on the Postgres site discussing what is and isn't optimizable -- can you recommend any resources for this? — Jack R-G
– Jack R-G, Commented Sep 2, 2011 at 16:10

jperelli · Accepted Answer · 2011-08-30 06:13:11Z

4

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)

answered Aug 30, 2011 at 6:13

jperelli

7,2276 gold badges54 silver badges88 bronze badges

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

2 Comments

Jack R-G Over a year ago

Thanks for teaching me how to fish! ;) If my testing shows that it doesn't optimize it then I have no clue how to change it so that it will optimize. I couldn't find any docs on the Postgres site discussing what is and isn't optimizable -- can you recommend any resources for this?

jperelli Over a year ago

You must read the Postgis docs postgis.refractions.net/documentation/manual-1.5/reference.html In each function documentation you'll see "notes", some of them say that they will use indexes whenever they are available, like the ST_Dwithin function.

unmounted · Accepted Answer · 2011-08-30 00:26:53Z

1

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.

answered Aug 30, 2011 at 0:26

unmounted

34.6k16 gold badges64 silver badges61 bronze badges

3 Comments

Jack R-G Over a year ago

By points, do you mean integers? I'm trying to pin distances down to, say, 25 miles (which is less than a full degree) so fractions of degrees count. If by points, you mean discrete locations (a single point on the plane of the earth) vs. areas (e.g. a city or state) then you are correct that I am using distance between two points. I do not want to incur the processing overhead of calculating the most precise distance so I'm using the low-overhead alternative of finding all points in the database within an x-mile box (not circle) around my location.

unmounted Over a year ago

A point is just a 2 or 3 dimensional record, like (1,1) in the Cartesian plane, which is 1.414 away from (0,0). It doesn't have to be an integer, (1.414, 1.414) is 2 away from (0,0). Postgres has support for these kinds of geometries (see the link) and for indexes on them. Simple example: say you have points 1 -> (123.4, 56.7), 2 -> (123.4, 67.8), and 3 -> (123.4, 65.4). Index by interleaving powers of 10 in the x,y coordinates: 3-> 10263544 1-> 10253647 2-> 10263748 . See how the nearest points are next to each other? Now you have a 1 dimensional problem! Indexing separately on longitude...

unmounted Over a year ago

... and latitude will help but it won't get you these kinds of efficiencies. See also here: blog.notdot.net/2009/11/… for more background.

Collectives™ on Stack Overflow

PostgreSQL Column Index Query Optimization Question

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related