4

I am trying to run the following query:

SELECT * FROM fences where ST_Intersects(geom, ST_GeomFromText('POINT(-73.990452 40.994184)', 4326))

When I run Explain Analyze I get the following:

"Index Scan using fencegeomindex1 on fences  (cost=0.41..8.68 rows=1 width=980) (actual time=0.231..0.353 rows=9 loops=1)"
"  Index Cond: (geom && '0101000020E61000008010C990637F52C06D1ADB6B417F4440'::geometry)"
"  Filter: _st_intersects(geom, '0101000020E61000008010C990637F52C06D1ADB6B417F4440'::geometry)"
"Planning time: 0.239 ms"
"Execution time: 0.393 ms"

When I run this query which is using a normal postgresql polygon:

SELECT * FROM fences WHERE poly @> '(-73.990452,40.994184)'

I get:

"Bitmap Heap Scan on public.fences  (cost=464.69..24194.22 rows=6229 width=980) (actual time=0.058..0.087 rows=9 loops=1)" 
"  Recheck Cond: (fences.poly @> '((-73.990452,40.994184))'::polygon)"
"  Heap Blocks: exact=7"
"  Buffers: shared hit=14"
"  ->  Bitmap Index Scan on fencepolyindex1  (cost=0.00..463.13 rows=6229 width=0) (actual time=0.042..0.042 rows=9 loops=1)"
"        Index Cond: (fences.poly @> '((-73.990452,40.994184))'::polygon)"
"        Buffers: shared hit=7"
"Planning time: 0.094 ms"
"Execution time: 0.136 ms"

From a cost perspective the query using the postgis ST_Intersects appears to be much cheaper however it is taking much longer to plan and then to execute. Any idea why this is happening. I have Vacuumed and Analyzed the table.

Running the two different queries in our real application the one using PostGIS is netting us about a 1/3rd of the throughput as the plain old postgresql query.

One more note, we are currently Unioning multiple queries together to prevent extra network traffic not sure if this would affect how the queries are executed.

example:

SELECT * FROM fences where geom && ST_GeomFromText('POINT(-82.85966597206304 31.44405266605823)') UNION ALL
SELECT * FROM fences where geom && ST_GeomFromText('POINT(-82.85966597206304 31.44405266605823)')
Improve this question

1 Answer 1

Reset to default
3

Well, the absolute difference in planning times is fractions of a millisecond. The native PostgreSQL planner has an advantage because it doesn't actually do anything for a selectivity calculation, it just returns a constant value. The PostGIS selectivity function actually figures out the selectivity, using a test against a spatial histogram of the data. This is more complex (0.2ms more complex, it seems, which is pretty good, considering the amount of work it involves). It's also more accurate (see the "rows" estimate).

You could replace the PostGIS selectivity estimator if you want, over-riding it in the SQL for the opclass definition with a constant selectivity estimator. But since you're running braindead simple SQL, good planning is a luxury you don't really require, might as well just stick w/ native.

If your raison d'etre is just maximum reverse geocoding throughput you'll do even better with a dedicated reverse geocoder like http://twofishes.net/, or you can write your own pretty trivially with a few lines of python and the GEOS library. (Or a few lines of Java and the JTS library, if that's your bag)

Improve this answer
6
  • It is true that the difference in planning time is extremely small. I am just trying to figure out why if the query using the postgis geometry is yielding so much less throughput despite the huge reduction in rows that have to be scanned. Also reverse geocoding is not the aim here. I want to given a point find the polygons that contain that point. The polgons are custom shapes.
    – Nathan Case
    Commented Aug 12, 2015 at 18:54
  • Hm? They scan pretty much exactly the same data, they both have rtree-on-gist indexes that are build and scanned with basically the same code. PostGIS is just doing better at knowing how big the final output set will be, which is irrelevant in your query, but would be useful knowledge if your query was only a part of a much larger SQL expression.
    – Paul Ramsey
    Commented Aug 12, 2015 at 18:57
  • e.g. compare the results of EXPLAIN and EXPLAIN ANALYZE on your query. They should both return the same number of rows, PostGIS's initial guess about the size of the result set will just be more accurate. (Compare estimated vs actual.)
    – Paul Ramsey
    Commented Aug 12, 2015 at 18:59
  • So goes the "rows" in each explain not tell the actual number of rows that are expected to be scanned? If so it would seem like the postgis query is actually going to scan many less rows. Postgis returns 1 row and postresql returns 6230
    – Nathan Case
    Commented Aug 12, 2015 at 19:00
  • No, the "rows" is not how many will be scanned, it is how many rows the planner thinks the query will return for a given clause. It's the "estimate". Having a good estimate (as PostGIS does) is important for complex query planning, but not important for simple queries, which have simple plans. Run the queries with EXPLAIN ANALYZE to see the difference between estimates and actual.
    – Paul Ramsey
    Commented Aug 12, 2015 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.